Top 5 values by grouping

[Jessica]

Greetings!

I am trying to run a query that will give me the top 5
values by the groupings I have listed in the query. For
example, I have a tag number, date/time field and depth.
I want the top 5 values for each day for each tag number.
When I used the top 5 value I only get the top 5 values
for the entire dataset and not by my groupings.

Any suggestions?

Thanks!

Jessica

 

[John Spencer (MVP)]

Can you post the SQL that gives you the top 5 overall? Without that, a GUESS of
what you want might be something like:


SELECT TagNumber, DateField, Depth
FROM TableName
WHERE DEPTH in
(SELECT TOP 5 Temp.Depth
FROM TableName as Temp
WHERE Temp.DateField = TableName.DateField
AND Temp.TagNumber = TableName.TagNumber
ORDER BY Temp.Depth DESC)

 

[Guest]

Below is the SQL that I was running in order to try and
get the top 5 values per tag per day. What I got was top
5 values total for all tags all days.

SELECT TOP 5 tbl_depth.PTT_ID, tbl_depth.Date_only,
tbl_depth.depth_m
FROM tbl_TagInfo INNER JOIN tbl_depth ON
tbl_TagInfo.Year_PTT = tbl_depth.Year_PTT
WHERE (((tbl_depth.PTT_ID)="28664" Or (tbl_depth.PTT_ID)
="28665" Or (tbl_02to03_SR_pressure_arch.PTT_ID)="28666"
Or (tbl_depth.PTT_ID)="28670" Or (tbl_depth.PTT_ID)
="30036" Or (tbl_depth.PTT_ID)="30038" Or
(tbl_depth.PTT_ID)="30039") AND ((tbl_depth.date_time)
Between [Att_DateTime] And [End_BioDateTime]));

Thanks!

Jessica

 

[John Spencer (MVP)]

UNTESTED SQL follows. You didn't have any ORDER by clause in your query, so I'm
still not sure which TOP value you were trying to find.

SELECT tbl_depth.PTT_ID, tbl_depth.Date_only,
tbl_depth.depth_m
FROM tbl_TagInfo INNER JOIN tbl_depth ON
tbl_TagInfo.Year_PTT = tbl_depth.Year_PTT
WHERE (tbl_depth.PTT_ID in ("28664","28665","28670","30036","30038","30039")
or tbl_02to03_SR_pressure_arch.PTT_ID="28666")
AND (tbl_depth.date_time Between [Att_DateTime] And [End_BioDateTime])
AND tbl_depth.depth_m IN
(SELECT TOP 5 tmp.depth_m
FROM tbl_TagInfo as TI INNER JOIN tbl_depth as tmp
ON TI.Year_PTT = tmp.Year_PTT
WHERE tmp.Ptt_id = tbl_depth.Ptt_ID
AND tmp.Date_only = tbl_depth.Date_only
ORDER BY tmp.Depth_m)

Below is the SQL that I was running in order to try and
get the top 5 values per tag per day. What I got was top
5 values total for all tags all days.

SELECT TOP 5 tbl_depth.PTT_ID, tbl_depth.Date_only,
tbl_depth.depth_m
FROM tbl_TagInfo INNER JOIN tbl_depth ON
tbl_TagInfo.Year_PTT = tbl_depth.Year_PTT
WHERE (((tbl_depth.PTT_ID)="28664" Or (tbl_depth.PTT_ID)
="28665" Or (tbl_02to03_SR_pressure_arch.PTT_ID)="28666"
Or (tbl_depth.PTT_ID)="28670" Or (tbl_depth.PTT_ID)
="30036" Or (tbl_depth.PTT_ID)="30038" Or
(tbl_depth.PTT_ID)="30039") AND ((tbl_depth.date_time)
Between [Att_DateTime] And [End_BioDateTime]));

Thanks!

Jessica

-----Original Message-----
Can you post the SQL that gives you the top 5 overall? Without that, a GUESS of
what you want might be something like:


SELECT TagNumber, DateField, Depth
FROM TableName
WHERE DEPTH in
(SELECT TOP 5 Temp.Depth
FROM TableName as Temp
WHERE Temp.DateField = TableName.DateField
AND Temp.TagNumber = TableName.TagNumber
ORDER BY Temp.Depth DESC)

.