|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
September 20th, 2004, 08:54 PM
|
|||
|
|||
how to select only max date
Hi all,
I have a table with structure like: date ID status 09-01-2004 001 active 09-05-2004 001 active 09-10-2004 001 active 09-01-2004 002 active 09-05-2004 002 active 09-10-2004 002 active I want to pull only the latest date per each ID. So in the example above, I would pull record 3 and 6, because each has the latest date for each of the IDs. How can I do this thru SQL? Thanks for your help. Ben 
|
|
#2
September 20th, 2004, 09:04 PM
|
|||
|
|||
|
Dear Ben:
SELECT [date], ID, status FROM YourTable T WHERE [date] = (SELECT MAX([date]) FROM YourTable T1 WHERE T1.ID = T.ID) If you allow two rows with the same date and ID, then you may get more than one row for that ID. The query will not arbitrate between them for you. Please substitute the actual name of YourTable Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Mon, 20 Sep 2004 15:54:09 -0400, "Ben" wrote: Hi all, I have a table with structure like: date ID status 09-01-2004 001 active 09-05-2004 001 active 09-10-2004 001 active 09-01-2004 002 active 09-05-2004 002 active 09-10-2004 002 active I want to pull only the latest date per each ID. So in the example above, I would pull record 3 and 6, because each has the latest date for each of the IDs. How can I do this thru SQL? Thanks for your help. Ben
|
|
#3
September 23rd, 2004, 08:49 PM
|
|||
|
|||
|
Thanks Tom.
"Tom Ellison" wrote in message ... Dear Ben: SELECT [date], ID, status FROM YourTable T WHERE [date] = (SELECT MAX([date]) FROM YourTable T1 WHERE T1.ID = T.ID) If you allow two rows with the same date and ID, then you may get more than one row for that ID. The query will not arbitrate between them for you. Please substitute the actual name of YourTable Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Mon, 20 Sep 2004 15:54:09 -0400, "Ben" wrote: Hi all, I have a table with structure like: date ID status 09-01-2004 001 active 09-05-2004 001 active 09-10-2004 001 active 09-01-2004 002 active 09-05-2004 002 active 09-10-2004 002 active I want to pull only the latest date per each ID. So in the example above, I would pull record 3 and 6, because each has the latest date for each of the IDs. How can I do this thru SQL? Thanks for your help. Ben
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| QDE (Quick Date Entry) | Norman Harker | Worksheet Functions | 37 | September 5th, 2004 01:24 AM |
| Query to select records by latest date | Jan H | New Users | 2 | July 13th, 2004 05:11 AM |
| Aggregating Date Data into Weeks and Quarters | Roger | Running & Setting Up Queries | 3 | July 11th, 2004 05:56 PM |
| Using Combo Box to Select Date Range | Sahil | Running & Setting Up Queries | 2 | June 16th, 2004 01:22 PM |
| Formatting dates in Excel | bernrunner15 | New Users | 4 | May 11th, 2004 10:32 PM |
Linear Mode
