How to Use Distinct and Order by in Same Select Statement

How to use DISTINCT and ORDER BY in same SELECT statement?

The problem is that the columns used in the ORDER BY aren't specified in the DISTINCT. To do this, you need to use an aggregate function to sort on, and use a GROUP BY to make the DISTINCT work.

Try something like this:

SELECT DISTINCT Category, MAX(CreationDate) 
FROM MonitoringJob 
GROUP BY Category 
ORDER BY MAX(CreationDate) DESC, Category

ORDER BY and DISTINCT in same select statement

The best way to resolve this issue is to order by the column number position. E.G. If the column is in the 4th position 'order by 4'

SELECT DISTINCT 
COALESCE( M.[Team ADJ],D.[Team ADJ], M.[Name]) AS 'Sales Person',
COALESCE( D.[Team], M.[Team]) AS 'Sales Team',
isnull(D.[Daily Figure],0),

FROM  
[Daily] D
FULL OUTER JOIN
[Month] M ON D.[Name] = F.[Name]
ORDER BY 4 DESC

SELECT DISTINCT and ORDER BY number of same values

You should use GROUP BY and then ORDER:

SELECT word
FROM Table
GROUP BY Word
ORDER BY COUNT(*) DESC

Using distinct on a column and doing order by on another column gives an error

As far as i understood from your question .

distinct :- means select a distinct(all selected values should be unique).
order By :- simply means to order the selected rows as per your requirement .

The problem in your first query is
For example :
I have a table

ID name
01 a
02 b
03 c
04 d 
04 a

now the query select distinct(ID) from table order by (name) is confused which record it should take for ID - 04 (since two values are there,d and a in Name column). So the problem for the DB engine is here when you say
order by (name).........

SQL Server Select Distinct and Order By with CASE

As the error suggests, when you use select distinct, you have to order by the expressions in the select clause. So, your case is an issue as well as all the columns not from d.

You can fix this by using group by instead, and including the columns that you want to sort by. Because the case includes a column from s, you need to include the case (or at least that column) in the group by:

SELECT d.*
FROM Data d JOIN
     Customers c 
     ON c.Customer_Name = d.Customer_Name AND
        c.subMarket = d.subMarket JOIN Sort s 
     ON s.Market = c.Market
GROUP BY "d.*",
         (CASE s.sortBy WHEN 'Comp_Rank' THEN d.Comp_Rank
                        WHEN 'Market_Rank' THEN d.Market_Rank
                        ELSE d.Other_Rank
         END)
ORDER BY d.Customer_Name, d.Category, d.Tab, d.SubMarket,
         (CASE s.sortBy WHEN 'Comp_Rank' THEN d.Comp_Rank
                        WHEN 'Market_Rank' THEN d.Market_Rank
                        ELSE d.Other_Rank
         END)

Note that "d.*" is in quotes. You need to list out all the columns in the group by.

Trouble with query select distinct order by case

The grouping will already ensure you get distinct customer names. Just drop this modifier and you should be fine:

SELECT "CUSTOMER"."NAME", -- distinct keyword removed here
    Max("CUSTOMER"."NAME")
FROM "CUSTOMER"
WHERE
    "CUSTOMER".id >= 2
GROUP BY "CUSTOMER"."NAME"
ORDER BY CASE WHEN ("CUSTOMER"."NAME" = 'HAMZA' ) THEN 1 ELSE 2 END

SQL Select Distinct Values, but order by a different value

If there are multiple rows for the order, which date do you want to show? perhaps:

SELECT [orderId], MAX([datetime])
FROM [table]
GROUP BY [orderId]
ORDER BY MAX([datetime]) DESC

How to use DISTINCT ON but ORDER BY another expression?

This requirement is necessary to make DISTINCT ON work; to change the final order, you can add an outer query with another ORDER BY clause:

SELECT *
FROM (SELECT DISTINCT ON (s.id)
             s.id as subscription_id, subscription_carts.authorized_at, s.*
      FROM subscriptions s
         JOIN ...
      WHERE ...
      ORDER BY s.id, subscription_carts.authorized_at
     ) AS subq
ORDER BY authorized_at;

---

Related Topics