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 DESCyou 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 DESCWhat 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)The result is each name only once along with the most recent date for that name.
FROM AppointmentsKept ORDER BY MAX(ApptDate) DESC
Added 10/6/06
A reader sent in another suggested method for selecting Distinct with the most recent date:
select Name, ApptDateThanks!
from AppointmentsKept
group by Name
having AppDate = max(AppDate)
9 comments:
You don't need to use "SELECT DISTINCT" if you're grouping. You'll get the same results if you omit the DISTINCT keyword.
Oops... fixed.
Thanks for catching that. A cut and past error.
The following is what I usually utilize:
SELECT Name, MAX(ApptDate)
FROM AppointmentsKept
GROUP BY Name
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.
a GROUP BY is necessary for this to work
SELECT DISTINCT Name, MAX(ApptDate)FROM AppointmentsKept
GROUP BY Name
ORDER BY MAX(ApptDate) DESC;
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
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
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...
Mike,
Try a MIN(Time_Stamp) instead.
Post a Comment