June 1, 2012 at 12:08 pm
Hey all,
I realize this may seem like a basic question but it involves correlated sub-queries which I don't have a rigorous understanding of yet.
Let's say there's an Appointments table for a hair salon. It contains two fields: CustomerId (int) and AppointmentDate (DateTime)
I have a list of CustomerIds, and I want to select the customer ID and the latest appointment date for each customer in the list (let's say CustomerID's 1 through 5). How would I do this? I could look up the patients individually like this:
SELECT * FROM Appointments
WHERE CustomerId = 5
ORDER BY AppointmentDate DESC
I want to look up the latest appointment dates all at once. How would I do this? I would appreciate any help anyone can provide, especially since I think it would increase my understanding of SQL significantly. Thanks.
June 1, 2012 at 12:18 pm
Try Max(http://msdn.microsoft.com/en-us/library/ms187751.aspx) Appointmentdate with group by(http://msdn.microsoft.com/en-us/library/ms177673.aspx) on customerid.
June 1, 2012 at 12:27 pm
SQLUserC (6/1/2012)
Hey all,I realize this may seem like a basic question but it involves correlated sub-queries which I don't have a rigorous understanding of yet.
Let's say there's an Appointments table for a hair salon. It contains two fields: CustomerId (int) and AppointmentDate (DateTime)
I have a list of CustomerIds, and I want to select the customer ID and the latest appointment date for each customer in the list (let's say CustomerID's 1 through 5). How would I do this? I could look up the patients individually like this:
SELECT * FROM AppointmentsWHERE CustomerId = 5
ORDER BY AppointmentDate DESC
I want to look up the latest appointment dates all at once. How would I do this? I would appreciate any help anyone can provide, especially since I think it would increase my understanding of SQL significantly. Thanks.
Something like this.
select
CustomerId,
max(AppointmentDate) as LatestDate
from
dbo.Appointments
group by
CustomerId
order by
CustomerId; -- Just to put them in some sort of order
June 1, 2012 at 12:35 pm
I strongly suspect you will want to see more details on the last appointment than just the date:
SELECT
CustomerId, AppointmentDate, ...
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY CustomerId
ORDER BY AppointmentDate DESC) AS row_num
FROM dbo.Appointments a
/*INNER JOIN dbo.ListOfCustIds lci ON lci.CustomerId = a.CustomerId*/
WHERE CustomerId = 5 --for cust list, put in a table and join to it, as shown above
) AS derived
WHERE
row_num = 1
ORDER BY
CustomerId
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 1, 2012 at 12:41 pm
ScottPletcher (6/1/2012)
I strongly suspect you will want to see more details on the last appointment than just the date:
SELECT
CustomerId, AppointmentDate, ...
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY CustomerId
ORDER BY AppointmentDate DESC) AS row_num
FROM dbo.Appointments a
/*INNER JOIN dbo.ListOfCustIds lci ON lci.CustomerId = a.CustomerId*/
WHERE CustomerId = 5 --for cust list, put in a table and join to it, as shown above
) AS derived
WHERE
row_num = 1
ORDER BY
CustomerId
Maybe, then you could also do this:
select
a.* -- to save time, should define all columns you want to return
from
dbo.Appointments a
inner join (
select
CustomerId,
max(AppointmentDate) as LatestDate
from
dbo.Appointments
group by
CustomerId
)dt
on (a.CustomerId = dt.CustomerId and a.AppointmentDate = dt.LatestDate)
order by
a.CustomerId;
June 1, 2012 at 1:04 pm
Maybe, then you could also do this:
select
a.* -- to save time, should define all columns you want to return
from
dbo.Appointments a
inner join (
select
CustomerId,
max(AppointmentDate) as LatestDate
from
dbo.Appointments
group by
CustomerId
)dt
on (a.CustomerId = dt.CustomerId and a.AppointmentDate = dt.LatestDate)
order by
a.CustomerId;
I'd have done that on SQL 7.0 or SQL 2000; on SQL 2005+, the windowing functions are much easier -- and faster.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 1, 2012 at 1:39 pm
Thanks very much for these replies. They gave me some useful stuff to research.
June 1, 2012 at 2:02 pm
ScottPletcher (6/1/2012)
Maybe, then you could also do this:
select
a.* -- to save time, should define all columns you want to return
from
dbo.Appointments a
inner join (
select
CustomerId,
max(AppointmentDate) as LatestDate
from
dbo.Appointments
group by
CustomerId
)dt
on (a.CustomerId = dt.CustomerId and a.AppointmentDate = dt.LatestDate)
order by
a.CustomerId;
I'd have done that on SQL 7.0 or SQL 2000; on SQL 2005+, the windowing functions are much easier -- and faster.
The last time I posted something similar to this using the row_number() function, someone else came along and posted the group by solution. Always seems to be more than one way to do things.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply