DISTINCT() and ORDERBY issue

select distinct order by another column
how to use distinct and orderby together in sql server
sql stuff distinct order by
select distinct order by case
distinct order by postgres
order by items must appear in the select list if select distinct is specified.
sql distinct
distinct order by mysql

I am learning about LINQ-to-SQL and everything was going well until something strange happened:

I tried to make an example of distinct, so, using the Northwind dabatase I wrote the following query:

var query = 
    from o in db.Orders
    orderby o.CustomerID
    select new
    {
        o.CustomerID
    };

If I print the SQL generated by LINQ-to-SQL for the query stored in query it looks like this:

SELECT [t0].[CustomerID]
FROM [dbo].[Orders] AS [t0]
ORDER BY [t0].[CustomerID]

So, as usual, the query brings all the CustomerID for each Order in the Orders table ordered alphabetically.

But! If I use the Distinct() method like this:

var query = (
    from o in db.Orders
    orderby o.CustomerID
    select new
    {
        o.CustomerID
    }).Distinct();

The query brings the expected results of the Distinct clause, but the CustomerIDs are not ordered despite I wrote orderby o.CustomerID!

The SQL query for this second LINQ query is the following:

SELECT DISTINCT [t0].[CustomerID]
FROM [dbo].[Orders] AS [t0]

As we can see **the ORDER BY clause is missing. Why is that?

Why does the ORDER BY clause disappears when I use the Distinct() method?

From the Queryable.Distinct documentation;

The expected behavior is that it returns an unordered sequence of the unique items in source.

In other words, any order the existing IQueryable has is lost when you use Distinct() on it.

What you want is probably something more like this, an OrderBy() after the Distinct() is done;

var query = (from o in db.Orders
             select new
             {
                 o.CustomerID
             }).Distinct().OrderBy(x => x.CustomerID);

SELECT DISTINCT and ORDER BY, ORDER BY items must appear in the select list if SELECT DISTINCT is The issue is that using either one, the results comes back ordered as if Eric -- MAX() returns the highest value, not the lowest, so you'd use MIN(). re: SELECT DISTINCT and ORDER BY Really Nice suggestions. Thanks Jeff Smith :) Stewy 2008-07-15: re: SELECT DISTINCT and ORDER BY I have an issue with both DISTINCT and GROUP BY. The issue is that using either one, the results comes back ordered as if using order by.

Try rearranging the members to place the OrderBy after the Distinct. You'll have to revert to method chaining:

db.Orders.Select(o=>o.CustomerId).Distinct().OrderBy(id=>id);

This would be the more efficient way to set up the query in Enumerable Linq anyway, because the OrderBy would then operate only on the unique items and not on all of them. Also, according to MSDN, Enumerable.Distinct does not guarantee the return order of the elements anyway, so ordering before deduping is pointless.

How SQL DISTINCT and ORDER BY are Related – Java, SQL and , 1. SELECT DISTINCT length FROM film ORDER BY length The problem is, since we have synthetically added the extended sort key column  The solution is pretty s[i]mple: put the OrderBy operator after the Distinct one, like in the following queryB definition: var queryB = (from o in db.Orders select o.Employee.LastName) .Distinct().OrderBy( n => n ); Happy coding.

Due to the use of distinct, the order of the returned list is not guaranteed. LinqToSql is smart enough to recognize this, therefor it ignores it.

If you place the order by AFTER your Distinct, everything will happen as you desire.

var query = (from o in db.Orders
             select new
             {
                 o.CustomerID
             }).Distinct().OrderBy(o => o.CustomerID);

or

var query = db.Orders.Select(o => o.CustomerID).Distinct().OrderBy(o => o.CustomerID);

Please see this article for clarification:

http://programminglinq.com/blogs/marcorusso/archive/2008/07/20/use-of-distinct-and-orderby-in-linq.aspx

How SQL DISTINCT and ORDER BY are Related, SELECT DISTINCT length FROM film ORDER BY length The problem is, since we have synthetically added the extended sort key column  Test Case: use Query Builder and genearate query with DIstinct and Order BY And Liimit ( top , skip or something ;) ) Expected result would be to get distinct set of data ordered by key and with limit.

You can simulate ORDERBY and DISTINCT with this counstruction:

var distinctItems = employees.GroupBy(x => x.EmpID).OrderBy(x => x).Select(y => y.First());

Combining ORDER BY and DISTINCT, Without a transformation, a statement that contains both DISTINCT and ORDER BY would require two separate sorting steps-one to satisfy DISTINCT and one to​  The orderBy dynamic path generation was introduced in #582 and it works as expected with regular JPA Queries, e.g query.from(player).orderBy(player.team.name.asc()).list(player); generates proper JPQL select player from Player player lef

SQL - Select Distinct and Order By, But, the problem here is that the salespeople with salesperson_id values of 2 and 7 both have multiple orders in the Orders table. The query itself is not specific  var sizes = (from s in ent.competitors select s.canvasSize).Distinct().OrderBy(x => x); All the examples I found give this type of answer. But it sorts by canvasSize whereas, I want to sort by canvasLength.

Distinct and orderBy not working, You cannot order by created_at when using distinct() because you cannot return any specific rows with distinct. You need to specify what  OrderBy. OrderBy sorts the values of a collection in ascending or descending order. It sorts the collection in ascending order by default because ascending keyword is optional here. Use descending keyword to sort collection in descending order.

DISTINCT does not properly work with ORDER BY on function like , Your query is correct, I overlooked the detail that your LOWER() function takes an Combination of DISTINCT and ORDER BY is a problem. SELECT DISTINCT id FROM (subquery) dctrn_result ORDER BY last_name DESC; This select fails in mysql 5.7 but when adding the last_name to the select statement it runs without any problems Working example:

Comments
  • As an aside lambda syntax is much simpler for queries like this: var query = db.Orders.Select(o=>o.CustomerId).Distinct();
  • ... Yes it is: msdn.microsoft.com/en-us/library/…. The question is, why does Distinct not maintain the order of the input elements, when it so easily could by just yield-returning?
  • @KeithS Did you read your link... "The result sequence is unordered." It's not a bug when it's built into the design.
  • @JTorres I edited the answer based on exactly that statement in the link. In my previous comment I was agreeing with asawyer that it was indeed by design. The question remains; why did they design it that way when it could have so easily been implemented in an order-stable way?
  • @KeithS This might provide some insight msmvps.com/blogs/jon_skeet/archive/2010/12/30/…
  • @asawyer - So apparently Jon Skeet agrees with me; the "natural" implementation, and thus the naive expectation, is that Distinct would stream elements in the same order as they are input, removing subsequent dupes (the first duplicate is returned, others are suppressed); however he also notes this is in fact not the case with the built-in implementation.