CODEWITHSUNDEEP
sqlselect
Zain Ali
Zain Ali

Reputation: 15953

Distinct with order by clause

I want to get distinct Category and order there result by curdate column.

select distinct(Category)'Category' from sizes   order by curdate desc

But this simple query is generating errors.

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Upvotes: 0

Views: 562

Answers (6)

Costi Ciudatu
Costi Ciudatu

Reputation: 38195

I'm afraid you have the same constraint for SELECT DISTINCT as for GROUP BY clauses: namely, you cannot make use of a field that's not declared in the fields list, because it simply doesn't know which curdate to use when sorting in case there are several rows with different curdate values for the same Category.

EDIT: try something like:

SELECT Category FROM sizes GROUP BY Category ORDER BY MAX(curdate) DESC

Replace MAX with MIN or whatever suits you.

EDIT2: In this case, MAX(curdate) doesn't even have to be present in the field list since it's used in an aggregate function.

Upvotes: 7

Jaymz
Jaymz

Reputation: 6338

You look to be after a list of all the categories, with a date associated with each one. Whether you want the earliest first or latest first, you should be able to do one of the following:

SELECT Category, MAX(curdate) FROM sizes GROUP BY Category

Or:

SELECT Category, MIN(curdate) FROM sizes GROUP BY Category

Depending on whether you want the most recent or earliest dates associated with each category. If you need the list to then be ORDERed by the dates, add one of the following onto the end:

ORDER BY MAX(curdate)
ORDER BY MIN(curdate)

Upvotes: 1

oezi
oezi

Reputation: 51797

as the error-message said, you can't order by a column that isn't selected wehen using SELECT DISTINCT (same problem as with GROUP BY...). change your query to this:

SELECT DISTINCT category, curdate FROM sizes ORDER BY curdate DESC

EDIT: replying to yourt comment:
if you want to select the distinct category with the last date for every category, you'll have to change your query a bit. i can think of two possibilities for this: using MAX() like Costi Ciudatu posted or doing some crazy stuff with subselects - the first one would be the better approach.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

with cte as
(
  select
    Category,
    [CurDate],
    row_number() over(partition by Category order by [CurDate]) as rn
  from sizes  
)
select 
  Category
from cte  
where rn = 1  
order by [CurDate]

Upvotes: 1

Rozwel
Rozwel

Reputation: 2020

Exactly what the error says, it cannot order a distinct list if the sort filed is not part of the select. Reason being is that there may be multiple sort values for each of the distinct values selected. If the data looks like this

Category    CurDate
AAA         1/1/2011
BBB         2/1/2011
AAA         3/1/2011

Should AA be before or after BBB in the distinct list? If you just ordered by the date without the distinct you would get it in both positions. Since SQL doesn't know which date should be associated with the distinct category it will not let you sort by the date.

Upvotes: 0

Orin
Orin

Reputation: 371

Curdate must be in your select statement also, right now you are only specifying Category

Upvotes: 0

Related Questions