Select TOP N records for each group

[agc1976]

Hi,

My table looks similar to this:

DOWNTIMES (
Date (PK),
Shift (PK),
Machine (PK),
DowntimeCode (PK),
SequenceNo (PK),
TotalTime
)

I need to obtain, for a specific period of time (From/To) the top 15 downtimes (in terms of time consumed) FOR EACH MACHINE.

The structure of the table cannot be changed. Any suggestions?

Thanks,
Alfred

 

[hirick]

I think you have to do it with a cursor and a temp table. Retrieve all the machine numbers into your cursor, loop through it to append the top 15 rows for that machine. Then select * from the temp table.

 

[tlbroadbent]

You should be able to use a subquery in this manner.
[tt]
SELECT Machine, TotalTime
FROM DOWNTIMES AS d
WHERE [Date] Between FromDate AND ToDate
AND TotalTime IN
(SELECT TOP 15 TotalTime
FROM DOWNTIMES
WHERE [Date] Between FromDate AND ToDate
AND Machine = d.Machine
ORDER BY TotalTime DESC)
ORDER BY Machine, TotaTime[/tt] Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[agc1976]

Thanks for your support. I went with the cursor. I initially didn't want to use a cursor because of performance issues, but I guess there is no other solution to my knowledge.

Using the [IN] clause would work on most cases, but what would happen if I have two downtimes that add up to exactly the same number of minutes? My guess is that it will return 16 downtimes instead of 15... (just a guess)...

Since I don't have a field that is unique in my table I don't think I can use the [IN] approach... unless there's something else about it that I still haven't learned...

Performance with the cursor is better than I expected so I'm satisfied with it...

Thanks to both.
Alfred

PS. If a better approach comes to mind please let me know.