June 26, 2007 at 6:42 am
I need to select fields with unique data in one field. I tried to use the distinct keyword however the entire record is not unique just one column.
How do I query for the information required?
Example Data:
1234 John Doe 12/1/2007 2pm
1234 John Doe 12/1/2007 1pm
4321 John Smith 11/1/2007 3pm
1234 John Doe 11/1/2007 1pm
...
I would like the results of:
1234 John Doe 12/1/2007 2pm
4321 John Smith 11/1/2007 3pm
The unique field I would like is the first column. However when I use the distinct it does not give the needed result.
Thanks for the help,
Kirk
June 26, 2007 at 7:35 am
DECLARE @t TABLE
(
PersonID int NOT NULL
,FirstName varchar(20) NOT NULL
,Surname varchar(20) NOT NULL
,Added datetime NOT NULL
)
INSERT INTO @t
SELECT 1234, 'John', 'Doe', '20071201 14:00' UNION ALL
SELECT 1234, 'John', 'Doe', '20071201 13:00' UNION ALL
SELECT 4321, 'John', 'Smith', '20071101 15:00' UNION ALL
SELECT 1234, 'John', 'Doe', '20071101 13:00'
SELECT T.*
FROM @t T
JOIN (
SELECT T1.PersonID, MAX(T1.Added) AS Added
FROM @t T1
GROUP BY T1.PersonID
) D
ON T.PersonID = D.PersonID
AND T.Added = D.Added
June 28, 2007 at 7:20 am
I do not fully understand what is going on in the query
SELECT T.*
FROM @t T
JOIN (
SELECT T1.PersonID, MAX(T1.Added) AS Added
FROM @t T1
GROUP BY T1.PersonID
) D
ON T.PersonID = D.PersonID
AND T.Added = D.Added
Can you explain it?
June 28, 2007 at 12:10 pm
@t is the table variable he created to hold the data, you would insert your actual table name there.
(SELECT T1.PersonID, MAX(T1.Added) AS Added
FROM @t T1
GROUP BY T1.PersonID ) D
selects the latest date for each personid and returns the result set aliased as D
SELECT T.*
FROM @t T
JOIN
----
ON T.PersonID = D.PersonID
AND T.Added = D.Added
joins the original table to the result set aliased as D to get back the other fields in the tbale but only return the ones that match the last record added based on the date.
This assumes that you want the last record added for each personid and that the dates added for each personid are unique. You could use MIN(T1.Added) if you wanted the first one. If you have a primary key on the table you could also use that in place of the T1.added. It depends on which record you want to get back in the case of of multiple recods with the same personid.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply