Top 100 records

  • Thread starter Thread starter John Waller
  • Start date Start date
J

John Waller

I'm trying to construct a SQL query in Access 2002 which will select 100
records - and only 100 records - based on date of entry (fieldname:
PrintDate).

I have:

"SELECT TOP 100 Recordings.Artist, Recordings.RecordingTitle,
Tracks.TrackTitle, Source, InstrumentalVocal, Tracks.PrintDate FROM
Recordings, Tracks WHERE Tracks.RecordingID = Recordings.RecordingID ORDER
BY PrintDate DESC;"

working OK but I can now see that SELECT TOP 100 does not decide between
equal values so if records 98-106 are equal values for PrintDate then I get
106 records returned instead of 100.

How can I return just the top 100 by PrintDate even if records 98-106 have
the same value for PrintDate?
 
Add the field (or fields) to your ORDER BY clause that together with
PrintDate will uniquely identify each track. For example, if Tracks has a
field named TrackID with unique values, it might be as simple as changing
your ORDER BY clause to:

ORDER BY
PrintDate DESC,
Tracks.TrackID

In case of ties, the query will use TrackID to "break" them.
 
Back
Top