Page 1 of 1

Sorting within a GROUP BY

Posted: Tue Jun 27, 2006 1:44 pm
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

Re: Sorting within a GROUP BY

Posted: Tue Jun 27, 2006 1:47 pm
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.

Posted: Tue Jun 27, 2006 2:11 pm
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);

Posted: Thu Jun 29, 2006 6:27 am
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... :)

:)