More then 5 result rows with SELECT TOP 5 *

  • Thread starter Thread starter Versteijn
  • Start date Start date
V

Versteijn

Hello all,

I have the following test table in Access 2003:

Id test dat
1 € 5,00 1-4-2004
2 € 0,00 1-5-2004
3 € 0,00 1-6-2004
4 € 0,00 1-7-2004
5 € 0,00 1-8-2004
6 € 0,00 1-9-2004
7 € 0,00 1-10-2004
8 € 0,00 1-10-2004
9 € 0,00 1-9-2004
10 € 0,00 1-8-2004
11 € 0,00 1-7-2004

Now I do the following query:
SELECT TOP 5 *
FROM tabel1
ORDER BY dat DESC;

I expect to get 5 rows, i.e. the rows with ID 8, 7, 9, 6, and 10. I
get the following result table though:

Id test dat
8 € 0,00 1-10-2004
7 € 0,00 1-10-2004
9 € 0,00 1-9-2004
6 € 0,00 1-9-2004
10 € 0,00 1-8-2004
5 € 0,00 1-8-2004

As you can see, this result table has 6 rows instead of 5. Is this by
design? This only happens when I sort on a DateTime field, not on the
ID.

Regards,

Freek Versteijn.
 
Hi,


With JET, TOP N returns ties while with MS SQL Server, TOP N does not
return ties (by default) and limit arbitrary to exactly N records. With MS
SQL Server, to get Jet behavior, add WITH TIES. With Jet, to get MS SQL
Server, add the primary key to the ORDER BY clause.

ORDER BY dat DESC, ID;




Hoping it may help,
Vanderghast, Access MVP
 
Michel Walsh said:
Hi,


With JET, TOP N returns ties while with MS SQL Server, TOP N does not
return ties (by default) and limit arbitrary to exactly N records. With MS
SQL Server, to get Jet behavior, add WITH TIES. With Jet, to get MS SQL
Server, add the primary key to the ORDER BY clause.

ORDER BY dat DESC, ID;
Click to expand...

Thanks for clarifying this to me!

Regards,

Freek Versteijn
 
Back
Top