order by before distinct.

[Jim Drewes]

This is basically the problem. I have a list of data that (generalized to shoes) would look something like this..

SHOE NAME SHOE TYPE ID DATE SHOE MADE
shoe1 1 1-2-03
shoe2 2 2-3-04
shoe3 3 3-4-05
shoe4 2 4-5-06

Now, if I want to get the most recent shoe types manufactured, distinctly (2,3,1)

So, the query is:

select distinct shoe_type_id
from shoe_table
order by date_shoe_made desc

Well, this actually gives me the result (3,2,1). I'm assuming this is because it executes the distinct clause before the order by. So, after the group by, the shoe4 record drops out, and then the table is sorted by date, and you get the wrong results.

So, how can I execute the query properly?