Sorting within a GROUP BY

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Sorting within a GROUP BY

Post by anjanesh »

Code: Select all

SELECT * FROM `tbl1` GROUP BY `field1`
I also have a field called `Entered` as datetime.

Problem is, there are many same values of `field1` - and I want the one that has `Entered` in the begining - a GROUP BY is picking the last one entered in the database or something like that.

How do I sort within a GROUP BY ?

Thanks
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Sorting within a GROUP BY

Post by RobertGonzalez »

EDIT | Removed my original answer. It was bad.

Can you try to SELECT DISTINCT WHERE to try to grab only one record WHERE the datetime is most recent? Not sure if that will do what you want.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

I tried to do a similar thing mentioned here, and this forced me to cool reboot.

Code: Select all

SELECT article, dealer, price, `Entered`
FROM   shop s1
WHERE UNIX_TIMESTAMP(s1.`Entered`) = (SELECT MAX(UNIX_TIMESTAMP(s2.`Entered`))
              FROM shop s2
              WHERE s1.article = s2.article);
dreamline
Forum Contributor
Posts: 158
Joined: Fri May 28, 2004 2:37 am

Post by dreamline »

I'd do something like this:
select min(field1) from tbl1 order by field1 limit 0,1;

and if that doesn't give the correct result then try:
select * from tbl1 order by field1 desc limit 0,1


and to answer your question: just before the group by you can do an order by... :)

:)
Post Reply