Tuesday, September 26, 2006

SQL: Select Distinct with Most Recent Date

I'm going to simplify it, but let's start here: suppose you have a database that keeps track of patients who show up to their doctor appointments. In that database, you have a table called AppointmentsKept; in that table you have two columns: Name and Date.

Name = Patient Name
ApptDate = Date of Appointment

So if a patient has shown up to five appointments, his or her name will be in there five times with five different dates (one for each appointment). How can you get out each name only once, along with the most recent appointment date?

If you try
SELECT DISTINCT Name, ApptDate from AppointmentsKept ORDER BY AppDate DESC
you end up getting each name listed multiple times, once for each date because each appointment date is different (distinct).

Same things happens if you try
SELECT Name, ApptDate from AppointmentsKept GROUP BY Name, ApptDate ORDER BY AppDate DESC
What you need to do is reduce ApptDate down to a distinct value as well. Here's what I ended up with:
SELECT DISTINCT Name, MAX(ApptDate)
FROM AppointmentsKept ORDER BY MAX(ApptDate) DESC
The result is each name only once along with the most recent date for that name.

Added 10/6/06
A reader sent in another suggested method for selecting Distinct with the most recent date:
select Name, ApptDate 
from AppointmentsKept
group by Name
having AppDate = max(AppDate)
Thanks!

9 comments:

Rob Mayoff said...

You don't need to use "SELECT DISTINCT" if you're grouping. You'll get the same results if you omit the DISTINCT keyword.

RetroWebDev said...

Oops... fixed.

Thanks for catching that. A cut and past error.

Anonymous said...

The following is what I usually utilize:

SELECT Name, MAX(ApptDate)
FROM AppointmentsKept
GROUP BY Name

John said...

I have a team schedule and I want to select by date closest to the current date. How should I write that?

teamvsteam = game
date = date of game

Thanks.

Joshua said...

a GROUP BY is necessary for this to work

SELECT DISTINCT Name, MAX(ApptDate)FROM AppointmentsKept
GROUP BY Name
ORDER BY MAX(ApptDate) DESC;

Whiz said...

How would I include additional fields? It seems to be requiring me to group by those additional fields as well, but then it just displays all records.

This works fine:

SELECT DISTINCT Data.`Teller #`, Max(Data.Date) AS 'Date'
FROM `F:\Teller Currency Recap Report\Teller Currency Recap Data`.Data Data
GROUP BY Data.`Teller #`
ORDER BY Max(Data.Date) DESC

But thise displays all records:

SELECT DISTINCT Data.`Teller #`, Max(Data.Date) AS 'Date', Data.Starting, Data.`In's`, Data.`Out's`, Data.`Over's/Short's`
FROM `F:\Teller Currency Recap Report\Teller Currency Recap Data`.Data Data
GROUP BY Data.`Teller #`, Data.Starting, Data.`In's`, Data.`Out's`, Data.`Over's/Short's`
ORDER BY Max(Data.Date) DESC

Anonymous said...

How would the reverse be accomplished?

I would like to SELECT (for deletion) all but the most recent record with the most recent time_stamp.

Here is the statement that get me the most recent records.

SELECT [COMPUTER_NAME], MAX(Time_Stamp)
FROM [COMPUTERS]
GROUP BY [COMPUTER_NAME]
ORDER BY [COMPUTER_NAME]

I need the reverse of this, please.

Thanks,

Mike

Broken Dreams said...

Hi i am also tring to get the most recent record from my table my table is like this -

CREATE TABLE dbo.Orders(
OrderId INT NOT NULL,
OrderDate DATETIME,
Amount INT)

I have inserted a values

INSERT INTO dbo.orders
(OrderId,OrderDate,Amount)
VALUES (1,'1-1-2011',100)
INSERT INTO dbo.orders
(OrderId,OrderDate,Amount)
VALUES (2,'2-1-2011',200)
INSERT INTO dbo.orders
(OrderId,OrderDate,Amount)
VALUES (6,'1-1-2011 05:00:02',800)
INSERT INTO dbo.orders
(OrderId,OrderDate,Amount)
VALUES (7,'4-2-2011 06:00:02',700)
INSERT INTO dbo.orders
(OrderId,OrderDate,Amount)
VALUES (8,'4-2-2011 07:00:02',800)

i use the below query to get most recent order but it returns all the rows

SELECT OrderId ,MAX(OrderDate) AS MAX_Date
FROM Orders GROUP BY OrderId,OrderDate
HAVING OrderDate = MAX(OrderDate)

Please help me finding this...

Anonymous said...

Mike,

Try a MIN(Time_Stamp) instead.