|
Subject:
|
select top problem
|
|
Posted By:
|
keyvanjan
|
Post Date:
|
1/6/2006 3:13:24 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"
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/6/2006 3:36:44 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.
|
|
Reply By:
|
joefawcett
|
Reply Date:
|
1/6/2006 4:20:34 AM
|
You can also mtell the parser to not treat a keyword as a keyword by surrounding it with brackets:SELECT Top 10 NewsID,Headline,Hits FROM News Where Active= True order By [DATE] DESC
--
Joe (Microsoft MVP - XML)
|
|
Reply By:
|
Anantsharma
|
Reply Date:
|
1/6/2006 4:36:40 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
|
|
Reply By:
|
joefawcett
|
Reply Date:
|
1/6/2006 4:38:59 AM
|
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)
|
|
Reply By:
|
keyvanjan
|
Reply Date:
|
1/7/2006 6:11:30 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 )"
|
