Top 100 records

[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?

 

[Brian Camire]

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.

 

[John Waller]

Thank you Brian.

That worked a treat.

So simple and logical