T
topdog
How can I do a Top 3 Group by query to give me the top 3 records in
each group where none of the fields is a primary key?
Thanks
each group where none of the fields is a primary key?
Thanks
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dale Fye
Need a little more info about your table structure and the fields you want
to use to determine what constitutes Top3.
Dale
to use to determine what constitutes Top3.
Dale
T
topdog
THe table is VISITS with fields MRN, VisitDate and Pulse
Since there are many visits for each MRN I just want the top 3 by date.
Thank you.
Since there are many visits for each MRN I just want the top 3 by date.
Thank you.
G
Gary Walter
Hi topdog,topdog said:THe table is VISITS with fields MRN, VisitDate and Pulse
Since there are many visits for each MRN I just want the top 3 by date.
You might try using Count method demonstrated by
Brian Camire
SELECT t1.MRN, t1.Pulse, t1.VisitDate
FROM VISITS AS t1
WHERE ((((SELECT Count(*) FROM VISITS As q
WHERE
q.MRN=t1.MRN
AND
q.VisitDate>=t1.VisitDate))<=3))
ORDER BY t1.MRN, t1.VisitDate DESC;
Example data:
MRN Pulse VisitDate
a 55 1/1/2004
b 66 1/1/2004
a 56 2/1/2004
b 60 2/1/2004
a 61 3/1/2004
b 59 3/1/2004
a 57 4/1/2004
b 69 4/1/2004
a 70 5/1/2004
b 64 5/1/2004
a 68 6/1/2004
b 57 6/1/2004
c 74 6/1/2004
query above would return:
MRN Pulse VisitDate
a 68 6/1/2004
a 70 5/1/2004
a 57 4/1/2004
b 57 6/1/2004
b 64 5/1/2004
b 69 4/1/2004
c 74 6/1/2004
Please respond back if I have misunderstood
or was not clear about something.
Good luck,
Gary Walter
T
topdog
Gary, Thanks for the great reply. I'm close but I'm getting more
than 3 records returned when VisitDate is null. So, I'll get 3
records for each MRN with visitdates and then I'm getting all the
other ones with no visit date.
How can I get the top 3 by date and include null visit date only if
the total count per MRN is less than 3. I never want more than 3
records per MRN.
Mrn VisitDate Pulse
a 3/1/01 77
a 2/1/01 88
a 66
b etc........
Thanks,
John
than 3 records returned when VisitDate is null. So, I'll get 3
records for each MRN with visitdates and then I'm getting all the
other ones with no visit date.
How can I get the top 3 by date and include null visit date only if
the total count per MRN is less than 3. I never want more than 3
records per MRN.
Mrn VisitDate Pulse
a 3/1/01 77
a 2/1/01 88
a 66
b etc........
Thanks,
John
G
Gary Walter
Hi John,
To handle ties you will need a primary key.
Add "ID" to your table (type "Autonumber")
and make it your primary key.
Then, this should work to get only top 3 records
for each mrn (handling ties).
SELECT t1.MRN, t1.Pulse, t1.VisitDate
FROM VISITS AS t1
WHERE t1.ID IN
(SELECT TOP 3 q.ID
FROM VISITS AS q
WHERE q.MRN = t1.MRN
ORDER BY q.VisitDate DESC, q.ID)
ORDER BY t1.MRN, t1.VisitDate DESC;
Good luck,
Gary Walter
To handle ties you will need a primary key.
Add "ID" to your table (type "Autonumber")
and make it your primary key.
Then, this should work to get only top 3 records
for each mrn (handling ties).
SELECT t1.MRN, t1.Pulse, t1.VisitDate
FROM VISITS AS t1
WHERE t1.ID IN
(SELECT TOP 3 q.ID
FROM VISITS AS q
WHERE q.MRN = t1.MRN
ORDER BY q.VisitDate DESC, q.ID)
ORDER BY t1.MRN, t1.VisitDate DESC;
Good luck,
Gary Walter
T
topdog
I was trying to keep the example simple but now that you say I need a
primary key I should tell you that my table is the join of TWO tables.
Am I hosed or is the away around this beast. Here's the actual
"table".
(SELECT tblVisits.MRN, tblTPN.DateIVAccessIn, tblTPN.IVAccessType
FROM tblVisits INNER JOIN tblTPN ON tblVisits.VisitID = tblTPN.VisitID
WHERE (Not tblTPN.DateIVAccessIn Is Null) OR (Not tblTPN.IVAccessType
Is Null))
Thanks,
John
primary key I should tell you that my table is the join of TWO tables.
Am I hosed or is the away around this beast. Here's the actual
"table".
(SELECT tblVisits.MRN, tblTPN.DateIVAccessIn, tblTPN.IVAccessType
FROM tblVisits INNER JOIN tblTPN ON tblVisits.VisitID = tblTPN.VisitID
WHERE (Not tblTPN.DateIVAccessIn Is Null) OR (Not tblTPN.IVAccessType
Is Null))
Thanks,
John
G
Gary Walter
Hi John,
Did you try just adding VisitID to SELECT clause
of query?
SELECT
tblVisits.VisitID,
tblVisits.MRN,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits
INNER JOIN
tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE (Not tblTPN.DateIVAccessIn Is Null)
OR (Not tblTPN.IVAccessType Is Null)
SELECT t1.MRN, t1.Pulse, t1.VisitDate
FROM yourquery AS t1
WHERE t1.VisitID IN
(SELECT TOP 3 q.ID
FROM yourquery AS q
WHERE q.MRN = t1.MRN
ORDER BY q.VisitDate DESC, q.VisitID)
ORDER BY t1.MRN, t1.VisitDate DESC;
Good luck,
Gary Walter
Did you try just adding VisitID to SELECT clause
of query?
SELECT
tblVisits.VisitID,
tblVisits.MRN,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits
INNER JOIN
tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE (Not tblTPN.DateIVAccessIn Is Null)
OR (Not tblTPN.IVAccessType Is Null)
SELECT t1.MRN, t1.Pulse, t1.VisitDate
FROM yourquery AS t1
WHERE t1.VisitID IN
(SELECT TOP 3 q.ID
FROM yourquery AS q
WHERE q.MRN = t1.MRN
ORDER BY q.VisitDate DESC, q.VisitID)
ORDER BY t1.MRN, t1.VisitDate DESC;
Good luck,
Gary Walter
T
topdog
I tried it but no luck. I get more than 3 records returned still.
Thank you again for your help and any ideas. I changed your q.ID to
q.VisitID because I assumed that was the intention. yes?
John
MRN DateIVAccessIn IVAccessType
1398174 2/5/2004 Peripheral
1398174 1/5/2004 Port-a-Cath
1398174 11/27/2003 PICC
1398174 11/4/2003 PICC
1398174 PICC
1398174 PICC
SELECT t1.MRN, t1.DateIVAccessIn, t1.IVAccessType
FROM (SELECT
tblVisits.VisitID,
tblVisits.MRN,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits
INNER JOIN
tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE (Not tblTPN.DateIVAccessIn Is Null)
OR (Not tblTPN.IVAccessType Is Null)) AS t1
WHERE t1.VisitID IN
(SELECT TOP 3 q.VisitID
FROM (SELECT
tblVisits.VisitID,
tblVisits.MRN,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits
INNER JOIN
tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE (Not tblTPN.DateIVAccessIn Is Null)
OR (Not tblTPN.IVAccessType Is Null)) AS q
WHERE q.MRN = t1.MRN
ORDER BY q.DateIVAccessIn DESC, q.VisitID)
ORDER BY t1.MRN, t1.DateIVAccessIn DESC;
Thank you again for your help and any ideas. I changed your q.ID to
q.VisitID because I assumed that was the intention. yes?
John
MRN DateIVAccessIn IVAccessType
1398174 2/5/2004 Peripheral
1398174 1/5/2004 Port-a-Cath
1398174 11/27/2003 PICC
1398174 11/4/2003 PICC
1398174 PICC
1398174 PICC
SELECT t1.MRN, t1.DateIVAccessIn, t1.IVAccessType
FROM (SELECT
tblVisits.VisitID,
tblVisits.MRN,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits
INNER JOIN
tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE (Not tblTPN.DateIVAccessIn Is Null)
OR (Not tblTPN.IVAccessType Is Null)) AS t1
WHERE t1.VisitID IN
(SELECT TOP 3 q.VisitID
FROM (SELECT
tblVisits.VisitID,
tblVisits.MRN,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits
INNER JOIN
tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE (Not tblTPN.DateIVAccessIn Is Null)
OR (Not tblTPN.IVAccessType Is Null)) AS q
WHERE q.MRN = t1.MRN
ORDER BY q.DateIVAccessIn DESC, q.VisitID)
ORDER BY t1.MRN, t1.DateIVAccessIn DESC;
G
Gary Walter
Hi John,
One solution *might be* to add a random number
to original query to use as tie-breaker.
qryOrig:
SELECT
tblVisits.VisitID,
RND(tblVisits.VisitID) AS TieBreaker,
tblVisits.MRN,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits
INNER JOIN
tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE (Not tblTPN.DateIVAccessIn Is Null)
OR (Not tblTPN.IVAccessType Is Null)
then,
SELECT t1.MRN, t1.Pulse, t1.VisitDate
FROM qryOrig AS t1
WHERE t1.TieBreaker IN
(SELECT TOP 3 q.TieBreaker
FROM qryOrig AS q
WHERE q.MRN = t1.MRN
ORDER BY
q.VisitDate DESC,
q.VisitID,
q.TieBreaker)
ORDER BY t1.MRN, t1.VisitDate DESC;
If this does not work, please read your email.
Thanks,
Gary Walter
One solution *might be* to add a random number
to original query to use as tie-breaker.
qryOrig:
SELECT
tblVisits.VisitID,
RND(tblVisits.VisitID) AS TieBreaker,
tblVisits.MRN,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits
INNER JOIN
tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE (Not tblTPN.DateIVAccessIn Is Null)
OR (Not tblTPN.IVAccessType Is Null)
then,
SELECT t1.MRN, t1.Pulse, t1.VisitDate
FROM qryOrig AS t1
WHERE t1.TieBreaker IN
(SELECT TOP 3 q.TieBreaker
FROM qryOrig AS q
WHERE q.MRN = t1.MRN
ORDER BY
q.VisitDate DESC,
q.VisitID,
q.TieBreaker)
ORDER BY t1.MRN, t1.VisitDate DESC;
If this does not work, please read your email.
Thanks,
Gary Walter
G
Gary Walter
Hi John,
I'm sorry I wasted your time with creating
a tiebreaker with RND(). It would have worked
by *making a table* so Tiebreaker was set in stone,
but obviously as I used it, "2 randoms" are never
going to match. What was I thinking? Argh!
I was just trying to give each record a unique
"record number" from 3 fields that could be exactly
the same. Actually, if we went to the trouble of
making a table, we could just as easily emptied
a table with an autonumber field, and filled it
with qryOrig, forgetting RND() altogether (especially
since RND() would have needed *more adaptation*
because matching floats is a fool's dream on a computer...
CLng(100000*RND(VisitID)), or something like that).
But...nevermind.
In looking at your example data, I don't
know what will be wrong with including
tblTPN's pk?
tblTPN:
TPNID (pk)
VisitID
DateIVAccessIn
IVAccessType
tblVisits:
VisitID
MRNnew
qryOrig:
SELECT
tblVisits.MRNnew,
tblTPN.TPNID,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits INNER JOIN tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE
((Not (tblTPN.DateIVAccessIn) Is Null))
OR ((Not (tblTPN.IVAccessType) Is Null))
ORDER BY tblVisits.MRNnew;
qryTop3:
SELECT
t1.MRNnew,
t1.IVAccessType,
t1.DateIVAccessIn
FROM qryOrig AS t1
WHERE (((t1.TPNID) In
(SELECT TOP 3 q.TPNID
FROM qryOrig AS q
WHERE
q.MRNnew = t1.MRNnew
ORDER BY
q.DateIVAccessIn DESC,
q.TPNID)))
ORDER BY
t1.MRNnew,
t1.DateIVAccessIn DESC;
My ISP's mailserver is down at the moment,
but I will try to send zip back "in the future."
Please respond back if I have misunderstood
one more time.
Good luck,
Gary Walter
I'm sorry I wasted your time with creating
a tiebreaker with RND(). It would have worked
by *making a table* so Tiebreaker was set in stone,
but obviously as I used it, "2 randoms" are never
going to match. What was I thinking? Argh!
I was just trying to give each record a unique
"record number" from 3 fields that could be exactly
the same. Actually, if we went to the trouble of
making a table, we could just as easily emptied
a table with an autonumber field, and filled it
with qryOrig, forgetting RND() altogether (especially
since RND() would have needed *more adaptation*
because matching floats is a fool's dream on a computer...
CLng(100000*RND(VisitID)), or something like that).
But...nevermind.
In looking at your example data, I don't
know what will be wrong with including
tblTPN's pk?
tblTPN:
TPNID (pk)
VisitID
DateIVAccessIn
IVAccessType
tblVisits:
VisitID
MRNnew
qryOrig:
SELECT
tblVisits.MRNnew,
tblTPN.TPNID,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits INNER JOIN tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE
((Not (tblTPN.DateIVAccessIn) Is Null))
OR ((Not (tblTPN.IVAccessType) Is Null))
ORDER BY tblVisits.MRNnew;
qryTop3:
SELECT
t1.MRNnew,
t1.IVAccessType,
t1.DateIVAccessIn
FROM qryOrig AS t1
WHERE (((t1.TPNID) In
(SELECT TOP 3 q.TPNID
FROM qryOrig AS q
WHERE
q.MRNnew = t1.MRNnew
ORDER BY
q.DateIVAccessIn DESC,
q.TPNID)))
ORDER BY
t1.MRNnew,
t1.DateIVAccessIn DESC;
My ISP's mailserver is down at the moment,
but I will try to send zip back "in the future."
Please respond back if I have misunderstood
one more time.
Good luck,
Gary Walter
T
topdog
Gary, You're a genius. This works ! Thank you so much for your
tenacious skill. It's very appreciated. I wish I was born smart!
Best regards,
John
SELECT
t1.MRN,
t1.IVAccessType,
t1.DateIVAccessIn
FROM (SELECT
tblVisits.MRN,
tblTPN.TPNID,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits INNER JOIN tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE
((Not (tblTPN.DateIVAccessIn) Is Null))
OR ((Not (tblTPN.IVAccessType) Is Null))
ORDER BY tblVisits.MRN) AS t1
WHERE (((t1.TPNID) In
(SELECT TOP 3 q.TPNID
FROM (SELECT
tblVisits.MRN,
tblTPN.TPNID,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits INNER JOIN tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE
((Not (tblTPN.DateIVAccessIn) Is Null))
OR ((Not (tblTPN.IVAccessType) Is Null))
ORDER BY tblVisits.MRN) AS q
WHERE
q.MRN = t1.MRN
ORDER BY
q.DateIVAccessIn DESC,
q.TPNID)))
ORDER BY
t1.MRN,
t1.DateIVAccessIn DESC;
tenacious skill. It's very appreciated. I wish I was born smart!
Best regards,
John
SELECT
t1.MRN,
t1.IVAccessType,
t1.DateIVAccessIn
FROM (SELECT
tblVisits.MRN,
tblTPN.TPNID,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits INNER JOIN tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE
((Not (tblTPN.DateIVAccessIn) Is Null))
OR ((Not (tblTPN.IVAccessType) Is Null))
ORDER BY tblVisits.MRN) AS t1
WHERE (((t1.TPNID) In
(SELECT TOP 3 q.TPNID
FROM (SELECT
tblVisits.MRN,
tblTPN.TPNID,
tblTPN.DateIVAccessIn,
tblTPN.IVAccessType
FROM tblVisits INNER JOIN tblTPN
ON tblVisits.VisitID = tblTPN.VisitID
WHERE
((Not (tblTPN.DateIVAccessIn) Is Null))
OR ((Not (tblTPN.IVAccessType) Is Null))
ORDER BY tblVisits.MRN) AS q
WHERE
q.MRN = t1.MRN
ORDER BY
q.DateIVAccessIn DESC,
q.TPNID)))
ORDER BY
t1.MRN,
t1.DateIVAccessIn DESC;