select top problem

keyvanjan · January 6th, 2006, 04:13 AM

Hi,
I have the following line.
I want to select top 10 records order by date desc.i use ms access db.
but the query returns all the records and when I omit order by date
it works fine.
How is that??

strSQL = "SELECT Top 10 NewsID,Headline,Hits FROM News Where Active= True order By DATE DESC"

Imar · January 6th, 2006, 04:36 AM

A question about an Access database in the SQL Server forum.... Hmmmmm....

Anyway, Date is a reserved word so unless you really want to order by today's date, you need to rename the column and update the ORDER BY clause accordingly.

Not sure what Access returns more results, but I think that by ordering on DATE, all records are treated equally...

Just a guess though...

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

joefawcett · January 6th, 2006, 05:20 AM

You can also mtell the parser to not treat a keyword as a keyword by surrounding it with brackets:

Code:

SELECT Top 10 NewsID,Headline,Hits  FROM News Where Active= True order By [DATE] DESC

--

Joe (Microsoft MVP - XML)

Anantsharma · January 6th, 2006, 05:36 AM

HI,

First of all the Query listed is not so correct. How can you Order by a Column which is not mentioned in Select List ???

If you have mentioned in original Query and its still not working, you can go for a subquery ...

Select TOP 10 [Date], col1,col2..From
( Select TOP 100 PERCENT * From Tbl order By [Date] DESC )

Hope yuo got the point.....**Please check with the syntax for inner Query...I tried to put the idea only.

B. Anant

joefawcett · January 6th, 2006, 05:38 AM

Quote:

quote:Originally posted by Anantsharma
How can you Order by a Column which is not mentioned in Select List ???

Why not?

--

Joe (Microsoft MVP - XML)

keyvanjan · January 7th, 2006, 07:11 AM

Thanks for your help.
The correct syntax was this:
strSQL= "Select TOP 10 Date,NewsID,Headline,Hits From ( Select TOP 10 date,NewsID,Headline,Hits From News order By Date DESC )"