ORDER BY SentDate DESC Ben Holness wrote:
>Hi all, > >I have a table with three columns: > >Destination varchar(254) >SentDate timestamp(14) >UserID varchar(32) > >I want to be able to get a list of the 100 most recently used numbers, based >on the timestamp (SentDate) column. > >I want to have them ordered such that the most recently used number is >first. > >Using the example data below, the list I would like to finish up with is >(order manually, but I think it is right): > >+--------------+ >| Destination | >+--------------+ >| 447812106183 | >| 447775906857 | >| 447781484245 | >| 447967305499 | >| 447812106198 | >| 447812106154 | >| 447781484234 | >| 447775906851 | >| 447967305450 | >+--------------+ > >How do I do this with an SQL statement? The one that I thought would work >does not take the most recent timestamp when grouping :( > >mysql> SELECT DISTINCT Destination, SentDate FROM OutboundMessages WHERE >UserID='2' GROUP BY Destination ORDER BY SentDate LIMIT 100; >+--------------+----------------+ >| Destination | SentDate | >+--------------+----------------+ >| 447967305499 | 20020529123518 | >| 447967305450 | 20020604213249 | >| 447775906851 | 20020604214940 | >| 447812106183 | 20020606173004 | >| 447781484234 | 20020612133629 | >| 447812106198 | 20020622194238 | >| 447775906857 | 20020622204826 | >| 447812106154 | 20020622205026 | >| 447781484245 | 20020626004507 | >+--------------+----------------+ >13 rows in set (0.37 sec) > >mysql> SELECT DISTINCT Destination, SentDate FROM OutboundMessages WHERE >UserID='2' ORDER BY SentDate LIMIT 100 >+--------------+----------------+ >| Destination | SentDate | >+--------------+----------------+ >| 447812106183 | 20020921134113 | >| 447775906857 | 20020921134001 | >| 447781484245 | 20020626004530 | >| 447781484245 | 20020626004529 | >| 447781484245 | 20020626004507 | >| 447781484245 | 20020626002957 | >| 447781484245 | 20020625174529 | >| 447967305499 | 20020623112634 | >| 447812106198 | 20020623112541 | >| 447775906857 | 20020622225446 | >| 447812106198 | 20020622225434 | >| 447812106154 | 20020622224006 | >| 447812106154 | 20020622205026 | >| 447775906857 | 20020622204826 | >| 447812106198 | 20020622194504 | >| 447812106198 | 20020622194250 | >| 447812106198 | 20020622194238 | >| 447967305499 | 20020621090855 | >| 447781484234 | 20020612133947 | >| 447781484234 | 20020612133629 | >| 447967305499 | 20020612133230 | >| 447967305499 | 20020606175612 | >| 447812106183 | 20020606174826 | >| 447812106183 | 20020606173004 | >| 447967305499 | 20020606171146 | >| 447967305499 | 20020606171002 | >| 447967305499 | 20020606122804 | >| 447967305499 | 20020605143105 | >| 447775906851 | 20020604214940 | >| 447967305499 | 20020604214058 | >| 447967305450 | 20020604213249 | >| 447967305499 | 20020529123940 | >| 447967305499 | 20020529123518 | >+--------------+----------------+ > >Many thanks, > >Ben > > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
