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 !!!

3
Contributors
2
Replies
3
Views
9 Years
Discussion Span
Last Post by tjbourke

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 !

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>

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.