distinct

Efficiently merge string arrays in .NET, keeping distinct values

I'm using .NET 3.5. I have two string arrays, which may share one or more values: string[] list1 = new string[] { "apple", "orange", "banana" }; string[] list2 = new string[] { "banana", "pear", "grape" }; I'd like a way to merge them into one array with no duplicate values: { "apple", "orange", "banana", "pear", "grape" } I can d...

Using DISTINCT inner join in SQL

I have three tables, A, B, C, where A is many to one B, and B is many to one C. I'd like a list of all C's in A. My tables are something like this: A[id, valueA, lookupB], B[id, valueB, lookupC], C[id, valueC]. I've written a query with two nested SELECTs, but I'm wondering if it's possible to do INNER JOIN with DISTINCT somehow. SELE...

Is there any difference between Group By and Distinct

I learned something simple about SQL the other day: SELECT c FROM myTbl GROUP BY C Has the same result as: SELECT DISTINCT C FROM myTble What I am curious of, is there anything different in the way an SQL engine processes the command, or are they truly the same thing? I personally prefer the distinct syntax, but I am sure it's m...

SQL distinct for 2 fields in a database

Can you get the distinct combination of 2 different fields in a database table? if so, can you provide the SQL example. ...

Python: DISTINCT on GQuery result set (GQL, GAE)

Imagine you got an entity in the Google App Engine datastore, storing links for anonymous users. You would like to perform the following SQL query, which is not supported: SELECT DISTINCT user_hash FROM links Instead you could use: user = db.GqlQuery("SELECT user_hash FROM links") How to use Python most efficiently to filter the r...

How do you create a Distinct query in HQL

Is there a way to create a Distinct query in HQL. Either by using the "distinct" keyword or some other method. I am not sure if distinct is a valid keywork for HQL, but I am looking for the HQL equivalent of the SQL keyword "distinct". ...

Multiple NOT distinct

Hi, I've got an MS access database and I would need to create an SQL query that allows me to select all the not distinct entries in one column while still keeping all the values. In this case more than ever an example is worth thousands of words: Table: A B C 1 x q 2 y w 3 y e 4 z r 5 z t 6 z y SQL magic Result: B C y w y e z r z...

Getting distinct records from a mysql query

In my application, there are publishers and categories. One publisher can belong to several categories. When I make my mysql transaction, it will return the same publisher record for each category it belongs to. Here's the query: SELECT grdirect_publisher.name, grdirect_publisher.short_description, grdirect_publisher.thum...

LINQ Distinct operator, ignore case?

Given the following simple example: List<string> list = new List<string>() { "One", "Two", "Three", "three", "Four", "Five" }; CaseInsensitiveComparer ignoreCaseComparer = new CaseInsensitiveComparer(); var distinctList = list.Distinct(ignoreCaseComparer as IEqualityComparer<string>).ToList(); It appears the CaseInsensit...

How to get distinct results in hibernate with joins and row-based limiting (paging)?

I'm trying to implement paging using row-based limiting (for example: setFirstResult(5) and setMaxResults(10)) on a Hibernate Criteria query that has joins to other tables. Understandably, data is getting cut off randomly; and the reason for that is explained here. As a solution, the page suggests using a "second sql select" instead of...

Normalizing a list of items in MSBuild

I am trying to get a list of all unit test assemblies under the root of my project. I can do this as follows: <CreateItem Include="**\bin\**\*.UnitTest.*.dll"> <Output TaskParameter="Include" ItemName="Items"/> </CreateItem> However, this will find the same DLLs multiple times since they exist in multiple sub-directories. Is there ...

SQL Select statement with Inner Join help

Hi, Here is my SQL Statement which is not returning DISTINCT Thread Titles. SELECT DISTINCT TOP 5 tblThread.Title, tblPost.Date FROM tblPost INNER JOIN tblThread ON tblPost.ThreadID = tblThread.ThreadID ORDER BY tblPost.Date DESC The common field between tblThread and tblPost is ThreadID. What I want this to do is return The late...

sql group by versus distinct

Why would someone use a group by versus distinct when there are no aggregations done in the query? Also, does someone know the group by versus distinct performance considerations in MySQL and SQL Server. I'm guessing that SQL Server has a better optimizer and they might be close to equivalent there, but in MySQL, I expect a significant...

C# Distinct on IEnumerable<T> with custom IEqualityComparer

Hi! Here's what I'm trying to do. I'm querying an XML file using LINQ to XML, which gives me and IEnumerable<T> object, where T is my "Village" class, filled with the results of this query. Some results are duplicated, so I would like to perform a Distinct() on the IEnumerable object, like so: public IEnumerable<Village> GetAllAlliance...

Selecting the distinct values from three columns with the max of a fourth where there are duplicates

I have a table with one numeric value (n) and three string values (a,b,c). How do I query this table so that I get only distinct values of (a,b,c) and if there are duplicates, take the maximum of the corresponding set of n values? ...

Getting Distinct records with date field?

Hello, I have a query where i want to get a distinct description by the latest date entered and the descriptions ID. I can get the disctinct part but i run into trouble with trying to get the ID since im using MAX on the date. Here is the query: SELECT DISTINCT Resource.Description, MAX(arq.DateReferred) AS DateReferred, arq.Assessment...

Why is there no Linq method to return distinct values by a predicate?

I want to get the distinct values in a list, but not by the standard equality comparison. What I want to do is something like this: return myList.Distinct( (x, y) => x.Url == y.Url ); I can't, there's no extension method in Linq that will do this - just one that takes an IEqualityComparer. I can hack around it with this: return myL...

"Distinct" results from SQL query using a mappings table

Hi, I have two tables tbl1 and tbl2, both with two columns, one for id and one for product. I want to extract the rows that are in both, i.e. rows where tbl1.id = tbl2.id and tbl1.product = tbl2.product and join the row from tbl1 and tbl2 into one row. I imagine this goes something like this: SELECT tbl1.\*, tbl2.\* FROM tbl1, tbl2 WH...

Optmizing MySQL GROUP BY or DISTINCT on large views

Consider a view consisting of several tables... for example a v_active_car, which is made up of the tables car joined on to body, engine, wheels and stereo. It might look something like this: v_active_cars view SELECT * FROM car INNER JOIN body ON car.body = body.body_id INNER JOIN engine ON car.engine = engine.engine_id I...

For Oracle SQL, getting a distinct value across multiple tables and columns

I have the following sample query, select o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE, l.COMPANY_ID from TABLE1 o, TABLE2 l where o.ENTRY_ID = l.ENTRY_ID and COMPANY_ID in (10, 11, 12, 13) that would return a set of data similar to the following: ENTRY_ID, DESCRIPTION, ENTRY_DATE, COMPANY_ID 1, Description 1, 2/12/2008, 10 2, Descript...