homepage Welcome to WebmasterWorld Guest from 107.22.78.233
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Ordering Before Grouping
How to attempt?
Willis




msg:1278166
 11:48 pm on May 8, 2004 (gmt 0)

In my particular database table, I have many rows which its common element is the `username`, now the most unique and method I wish to order on, is `time` (timestamp). In my code my base query currently is:

SELECT `username` FROM `download_counter` GROUP BY `username` ASC

Which then as expected, on page rows are ordered by username in alphabetical order. What I wish to do is, on page view, rows are ordered by the `time` value. However heres my two problems:

For starters it probably comes to no surprize that when using GROUP BY, the query remembers the first row it comes across and ignores the laters, so if there are three rows, oviously the first one it finds is the earliest of times, which is exact opposite of what I want. My other problem involves my attempt at a query:

SELECT `username` FROM `download_counter` ORDER BY `time` DESC GROUP BY `username`

this brings back:
#1064 - You have an error in your SQL syntax ... near 'GROUP BY `username`

I guess a ORDER cannot be before a GROUP.

But then what do I do? Is there a way maybe to query all results, then after that preform a GROUP?

 

Netizen




msg:1278167
 11:57 pm on May 8, 2004 (gmt 0)

SELECT `username` FROM `download_counter` GROUP BY `username` ASC

Try

SELECT `username`,max(`time`) as maxtime FROM `download_counter` GROUP BY `username` ORDER BY maxtime ASC

Willis




msg:1278168
 6:48 am on May 9, 2004 (gmt 0)

hey thanks, its workin perfect.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved