Return Top 5 Records For Group

[TimTDP]

I have the following SQL statement:

SELECT tblLoggerData.StoreRecordingLocationId, tblLoggerData.LoggerNumber, tblLoggerData.StartTime
FROM tblLoggerData;

I need the query to return the top 5 StartTimes for each LoggerNumber

How do I modify the SQL statement to do this?

Many thanks in advance

 

[MSAccessRookie]

I believe that the TOP command would be appropriate here. Of course, you might want to add an ORDER BY clause to clarify exactly what the top is.

SELECT TOP 5 tblLoggerData.StoreRecordingLocationId, tblLoggerData.LoggerNumber, tblLoggerData.StartTime
FROM tblLoggerData;

 

[Brianwarnock]

Top works over the whole file not within grouping, you need to write a ranking query.

I'm on an IPad at the moment and cannot do much, but in may 2010 I answered a question by James halliwell to find the 5 lowest figures by account, sorry I can't post the link but if you search all of his posts you will find it. He hasn't got too many.

Brian

 

[MSAccessRookie]

I believe that this might be the link that Brian was referring to.

http://www.access-programmers.co.uk/forums/showthread.php?t=193051

 

[Brianwarnock]

Thanks rookie, that name doesn't fit anymore BTW.

Brian

 

[MSAccessRookie]

Thanks Brian, but considering that I am still learning new things (or at least gaining new understandings about old things), I think I want to keep it for a while longer.