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
