Order By Group By Distinct Problem

[pedramphp]

hi folks ,

I have a Table names A with these fields (Id,Entry_Date,State)

I want to fetch the 5 recent records with Unique States And Order them Desc

so this is what I did

Select *
From A
Order By Entry_Date Desc
Limit 5

I tried many ways to do this , first of All I tried to work with Distinct but I figured out that Distinct is applied to a Row not a field
so

Select Distinct State,Id
From A
Order By Entry_Date Desc
Limit 5

it gives me the same result.
so I tried to solve the problem by group by

Select Distinct State,Id
From A
group by State
Order By Entry_Date Desc
Limit 5

but As you know the group by has to be before order and its giving me some other results

so I am wondering how can I do this !!!
Please help me on this

[nav33n]

I tried many ways to do this , first of All I tried to work with Distinct but I figured out that Distinct is applied to a Row not a field

What do you mean ?

select distinct state from A order by entry_date desc limit 5

will return 5 records with distinct state, sorted on entry_date in descending order.

Select Distinct State,Id From A group by State Order By Entry_Date desc Limit 5

This query will return 5 records with combination of distinct state and id sorted on entry_date in descending order.
for example,
state --- id
state1 --> 1
state1 --> 2
state2 --> 3
state3 --> 4
The above query will return state1 --> 1 as well as state1-->2, since they make unique combination !

[tjbourke]

I had this exact problem before, and I ran into again just yesterday. I finally came up with a simple solution, almost seems too simple. You need to use a subquery as a column of the select query. In that subquery is where you will do the ordering by date. When you do it all in a single query with GROUP BY ... ORDER BY ... the GROUP BY happens first, THEN the ORDER BY. You want to order first, so go with the subquery. I explain this in full detail in a blog post here:
<URL SNIPPED>