SQL 2005 ... Select Top 1, 2 and 3rd
In my Query below, I get back two records in my dataset. My issue is I am only filtering on one relationship type in the table, however there are several other relationship types I am not concerned with pulling back. So in my test patient below, Its pulling one with a ListOrder = to 1 and one with ListOrder =5.
What I need is to pull the Top 1 (Max) based of the created date and and make that the first one. I then need the next most recent one created and make that #2 and then finally the third most recent one based off Created.
I hope this makes sense. If not, I can explain more.
What I need is to pull the Top 1 (Max) based of the created date and and make that the first one. I then need the next most recent one created and make that #2 and then finally the third most recent one based off Created.
I hope this makes sense. If not, I can explain more.
SELECT
PatientProfileId,
pc.First,
pc.Middle,
pc.Last,
pc.Phone1,
ml.Description,
pr.Listorder
FROM
PatientRelationship pr
INNER JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
INNER JOIN Medlists ml ON pr.RelationshipTypeMId = ml.MedlistsId
WHERE
pr.PatientProfileId = '672' AND
pr.Type = 5 -- filter for personal contact type
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sure...row_number() is a good one. You can also check out rank(), dense_rank(), and ntile() for additional groupings if you'd like.
ASKER
These are new to me and I will need to read up on them. Thanks for the wonderful tips and teaching me. I love new tricks and I really appreciate you taking the time to help. Its very appreciated.
ASKER
chap -
How would I filter this to just give me the one where ranking = 1?
SELECT
PatientProfileId,
pc.First,
pc.Middle,
pc.Last,
pc.Phone1,
ml.Description,
pr.Listorder,
ranking = row_number() over(order by pr.created asc)
FROM
PatientRelationship pr
INNER JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
INNER JOIN Medlists ml ON pr.RelationshipTypeMId = ml.MedlistsId
WHERE
pr.PatientProfileId = '672' AND
pr.Type = 5 -- filter for personal contact type
AND ranking = 1
gives me:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ranking'.
How would I filter this to just give me the one where ranking = 1?
SELECT
PatientProfileId,
pc.First,
pc.Middle,
pc.Last,
pc.Phone1,
ml.Description,
pr.Listorder,
ranking = row_number() over(order by pr.created asc)
FROM
PatientRelationship pr
INNER JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
INNER JOIN Medlists ml ON pr.RelationshipTypeMId = ml.MedlistsId
WHERE
pr.PatientProfileId = '672' AND
pr.Type = 5 -- filter for personal contact type
AND ranking = 1
gives me:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ranking'.
select * from (
SELECT
PatientProfileId,
pc.First,
pc.Middle,
pc.Last,
pc.Phone1,
ml.Description,
pr.Listorder,
ranking = row_number() over(order by pr.created asc)
FROM
PatientRelationship pr
INNER JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
INNER JOIN Medlists ml ON pr.RelationshipTypeMId = ml.MedlistsId
WHERE
pr.PatientProfileId = '672' AND
pr.Type = 5 -- filter for personal contact type
) a
where ranking = 1
SELECT
PatientProfileId,
pc.First,
pc.Middle,
pc.Last,
pc.Phone1,
ml.Description,
pr.Listorder,
ranking = row_number() over(order by pr.created asc)
FROM
PatientRelationship pr
INNER JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
INNER JOIN Medlists ml ON pr.RelationshipTypeMId = ml.MedlistsId
WHERE
pr.PatientProfileId = '672' AND
pr.Type = 5 -- filter for personal contact type
) a
where ranking = 1
ASKER
SWEET. Thanks!
ASKER
Oooohhh I like this. Let me play with this some, but I think you got my friend! I will award points after I test it out some. You really are a life savior!