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