Looking at your output maybe the following query can work, give it a try: SELECT * FROM tablename WHERE id IN (SELECT MIN(id) FROM tablename GROUP BY EmailAddress) This will select only one row for each distinct email address, the row with the minimum id which is what your result seems to portray
I did a test on MS SQL 2005 using the following tables: A 400K rows, B 26K rows and C 450 rows. The estimated query plan indicated that the basic inner join would be 3 times slower than the nested sub-queries, however when actually running the query, the basic inner join was twice as fast … Read more
Did you add the Select() after the Where() or before? You should add it after, because of the concurrency logic: 1 Take the entire table 2 Filter it accordingly 3 Select only the ID’s 4 Make them distinct. If you do a Select first, the Where clause can only contain the ID attribute because all … Read more
Anthony Pegram has said it the best. Use the right tool for the job. I say this because a Distinct or HashSet isn’t that big different when it comes to performance. Use a HashSet when the collection should always hold only distinct stuffs. It also tells the programmer that you cant add duplicates to it. … Read more
There’s not an exact equivalent to convert a Postgresql query that makes use of SELECT DISTINCT ON to MySQL. Postgresql SELECT DISTINCT ON In Postgresql, the following query will eliminate all rows where the expressions (col1, col2, col3) match, and it will only keep the “first col4, col5 row” for each set of matched rows: … Read more
If you mean all columns whose email is unique: SELECT * FROM table WHERE email in (SELECT email FROM table GROUP BY email HAVING COUNT(email)=1);
A very typical approach to this type of problem is to use row_number(): select t.* from (select t.*, row_number() over (partition by number order by id) as seqnum from t ) t where seqnum = 1; This is more generalizable than using a comparison to the minimum id. For instance, you can get a random … Read more
It’s groups by needed properties and select: List<Product> result = pr.GroupBy(g => new { g.Title, g.Price }) .Select(g => g.First()) .ToList();
While similar names, the usage is different. I. Projections.distinct(Projections.property(“id”)); this statement would be translated into SQL Statement. It will be passed to DB Engine and executed as a SQL DISTINCT. See: 17.9. Projections, aggregation and grouping so e.g. this example: List results = session.createCriteria(Cat.class) .setProjection( Projections.projectionList() .add( Projections.distinct(Projections.property(“id”)) ) ) .list(); would seems like: SELECT … Read more
string[] result = list1.Union(list2).ToArray(); from msdn: “This method excludes duplicates from the return set. This is different behavior to the Concat(TSource) method, which returns all the elements in the input sequences including duplicates.”