MySQL

Order By Group By Distinct Problem

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2007

Posts: 16

Reputation: pedramphp is an unknown quantity at this point

Solved Threads: 0

pedramphp

Offline

Newbie Poster

Order By Group By Distinct Problem

Feb 20th, 2009

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT *
FROM A
ORDER BY Entry_Date Desc
LIMIT 5
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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT DISTINCT State,Id
FROM A
ORDER BY Entry_Date Desc
LIMIT 5
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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT DISTINCT State,Id
FROM A
GROUP BY State
ORDER BY Entry_Date Desc
LIMIT 5
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

Last edited by peter_budo; Feb 21st, 2009 at 4:35 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Nov 2007

Posts: 3,739

Reputation: nav33n is a jewel in the rough

Solved Threads: 330

nav33n

Offline

Senior Poster

Re: Order By Group By Distinct Problem

Feb 24th, 2009

•

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 ?

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
SELECT DISTINCT state FROM A ORDER BY entry_date desc LIMIT 5
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.

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
SELECT DISTINCT State,Id FROM A GROUP BY State ORDER BY Entry_Date desc LIMIT 5
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 !

Ignorance is definitely not bliss!

*PM asking for help will be ignored*

•

Join Date: Aug 2009

Posts: 1

Reputation: tjbourke is an unknown quantity at this point

Solved Threads: 0

tjbourke

Offline

Newbie Poster

Re: Order By Group By Distinct Problem

Aug 12th, 2009

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>

Last edited by peter_budo; Aug 13th, 2009 at 12:43 pm. Reason: Keep It Spam Free - Do not spam, advertise, plug your website, or engage in any other type of self promotion.