Selecting the last 5 records (1 Viewer)

[whitespaces]

Hello there people, hope you can help with this problem..

I am developing a database system to look at another systems data and use this to create graphs etc. I work for a railway maintenance company and the data from this other database comes in the form of one table (with about 300,000 records).

The table has the folloing fields (briefly)..

Route, Track ID, FromMileage, ToMileage, ReadingDate, Reading

The Mileage comes in as a number in yards (which I convert into the format x.yyyy - where x is the miles and yyyy is the yards eg. 3.0880 is 3 and a half miles) - anyway this probably aint important. What I want to do is create a query that pulls out for each route and track ID the last 5 readings - and there equivalent reading data.

The data looks something like this..

Route TrackID FromMile ToMile ReadingDate Reading
CGJ2 1100 283.0880 283.0880 01/08/97 2.83
CGJ2 1100 283.0880 283.0880 01/08/98 1.29
CGJ2 1100 283.0880 283.0880 15/03/99 1.41
CGJ2 2100 283.0880 283.0880 01/08/97 2.83
CGJ2 2100 283.0880 283.0880 01/08/97 2.83

I want to display for each route, track ID and mileage the last 5 readings. Surely this should be pretty straight forward, but note that.

The readings are not taken generally taken the same time from one to another. (The frequencies are rarely fixed)

The dates and approximate frequencies will be different for each Track ID / Route.

Although generally the mileages are split into eigth mile sections - this isn't always the case.

Some Mileage sections have readings for approximately every month, others for approximately every 2 years etc. Some mileage sections have lots of reading dates going back years.

Sorry to drag this out massively, but I would appreciate any help. Cheers.

 

[The_Doc_Man]

So if you want the last five entries....

reverse the sort order and take the first five entries.

 

[Jon K]

Assuming ReadingDate is a date field in your table, try this query (type or paste in the SQL View of a new query, replacing with the correct table name):-

SELECT *
FROM yourTable AS a
WHERE ReadingDate in (select Top 5 ReadingDate from yourTable where Route=a.Route and TrackID=a.TrackID order by ReadingDate Desc);


Hope it won't take too long for the query to run on 300,000 records (you may try it first on a smaller table.)

Remarks: If there are duplicate ReadingDates within a group, Top x may run into a tie and retrieve more than x records.

For Example:
For the five records shown, Top 1 will retrieve both records for CGJ2 2100 as they both bear the latest ReadingDate of 01/08/97.