Why doesn't Select Top 5 From news_table order by news_date work?

Question

I have a very very simple SQL string that MS Access doesn't seem to like.

Select Top 5 * From news_table Order By news_date

This is returning ALL rows from the news_table not just the Top 5.

I thought this was a simple SQL statement, apparently it is not.

Any fixes or idea as to why this is not working? Thanks!

Answer 1

Try Select Top 5 From news_table Order By news_date

Answer 2

Select Top n selects all records where records are equal, that is, if you select Top 2 and you have 20 date1 and 20 date2, 40 records will be selected.

If there is a keyfield,

 Select Top 5 * Order By KeyField

Should return 5 records, because keyfield is unique.

Answer 3

Does this do the trick?

select top 5 * 
from (select * from news_table order by news_date)

I don't know why the original doesn't work. Maybe it's a quirk with Access.

Edit: Business rules weren't specified. I didn't fully understand that the goal was to rank the table first and get the top 5 dates. It could have been to get the first 5 records and then rank them.

I can't reproduce your problem in my version of Access. Weird.

Answer 4

I've had trouble in the past with similar statements within Access. To debug similar problems, I always break down the query into multiple queries. I would suggest creating a query within access that does the select with the order by statement. Then perform the select top from that query.

Query1 => SELECT * FROM news_table Order By news_date;

Query2 => Select Top 5 From Query1;

Answer 5

I hardly ever write SQL using *. I'm wondering if perhaps Jet SQL is not parsing that correctly. Have you tried:

SELECT TOP 5 news_table.* FROM news_table ORDER BY news_date

That would be the way I'd always write it.

Yes, "SELECT TOP 5 *" ought to work, but if it doesn't, perhaps a little more specificity would help.

--
David W. Fenton
David Fenton Associates

Answer 6

Yes, This is working...

select top 5 *
from (select * from news_table order by news_date)