ORDER BY SentDate DESC
Ben Holness wrote:
Hi all,
I have a table with three columns:
Destinationvarchar(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 | 2002065446 |
| 447812106198 | 2002065434 |
| 447812106154 | 2002064006 |
| 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