problem with GROUP BY and ORDER BY

[Guest]

i usually use this query to display the last 10 entries from a sql table:

 

$query = "SELECT search_time, search_keywords FROM phpbb_popsearch ORDER BY search_time DESC limit 35"; 

 

as you can see it will order the results by "search_time" so the latest entries are listed first

 

normally the result would be something like this:

new entry

duplicate

another entry

bla bla

348237

543543

duplicate

 

but now i just want to include a "GROUP BY" in this sql request

$query = "SELECT search_time, search_keywords FROM phpbb_popsearch ORDER BY search_time DESC limit 35"; 

 

but with the GROUP BY my sql query isn't ordered correctly anymore.

 

the result would be something like this:

new entry

another entry

bla bla

348237

543543

duplicate

notice the "duplicate" entry was grouped together, but it's not in the second first position anymore ???

 

i was expecting a result like this:

new entry

duplicate

another entry

bla bla

348237

543543

Since entry called "duplicate" is the second last added entry....

 

i hope you understand what i mean ^^

 

thanks for help

[gerkintrigg]

I'm not sure I understand...

Try typing

echo $query;

and cut that code into phpMyAdmin. Occasionally you get weird results if you have variables in there.

 

Otherwise, encapsulate your field names... it helps:

$query = "SELECT `search_time`, `search_keywords` FROM `phpbb_popsearch` ORDER BY `phpbb_popsearch `.`search_time` DESC limit 35";

[PFMaBiSmAd]

You didn't post anything that shows the GROUP BY that you used. Best guess is that you did a GROUP BY something that would cause the output to be what you posted instead of what you wanted.

[Guest]

Damn, sorry

 

here is the query with the GROUP BY request

 

$query = "SELECT search_time, search_keywords FROM phpbb_popsearch GROUP BY search_keywords ORDER BY search_time DESC limit 35";

 

So it will group together entries with the same name, but it will lose its original position ! (i.e. first entries will not be first anymore once its grouped)