Problem using SELECT TOP and ORDER BY together in a query

[Ken Schaefer]

SQL is based on set theory. You are ordering the resultset by the StoryDate.
So you have 6 articles at the top of the list. How is the database supposed
to work out which 3 of those you want? So, it returns all 6

Instead, if you add another column to the ORDER By clause, you can further
limit the number of records returned, eg:

FROM
tblNews
ORDER BY
StoryDate DESC
, ID DESC

Cheers
Ken


"Pooley" <pooley@here.com> wrote in message
news:%23EAU%23$HaDHA.1744@TK2MSFTNGP12.phx.gbl...
: Hi
:
: I have a large news-based database that I am selecting from but when using
: SELECT TOP and ORDER BY in the same query, ORDER BY is overriding the
SELECT
: TOP number.
:
: i.e.
:
: Query = "SELECT TOP 3 ID, Headline, StoryDate, Author, Body FROM tblNews
: ORDER BY StoryDate DESC"
:
: returns all *6* rows with the latest StoryDate in the tblNews table (as
does
: SELECT TOP 1 or 2 or anything under 6). Without the ORDER BY part of the
: query, I only get 3 rows returned but, of course, they are not the latest
: dated one.
:
: Any idea how I get around this one, please?
:
: Thank you.
:
:

[Pooley]

Gotcha. Thanks Ken.


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:e$3LuuIaDHA.2336@TK2MSFTNGP09.phx.gbl...

> SQL is based on set theory. You are ordering the resultset by the

StoryDate.

> So you have 6 articles at the top of the list. How is the database

supposed

> to work out which 3 of those you want? So, it returns all 6
>
> Instead, if you add another column to the ORDER By clause, you can further
> limit the number of records returned, eg:
>
> FROM
> tblNews
> ORDER BY
> StoryDate DESC
> , ID DESC
>
> Cheers
> Ken
>
>
> "Pooley" <pooley@here.com> wrote in message
> news:%23EAU%23$HaDHA.1744@TK2MSFTNGP12.phx.gbl...
> : Hi
> :
> : I have a large news-based database that I am selecting from but when

using

> : SELECT TOP and ORDER BY in the same query, ORDER BY is overriding the
> SELECT
> : TOP number.
> :
> : i.e.
> :
> : Query = "SELECT TOP 3 ID, Headline, StoryDate, Author, Body FROM tblNews
> : ORDER BY StoryDate DESC"
> :
> : returns all *6* rows with the latest StoryDate in the tblNews table (as
> does
> : SELECT TOP 1 or 2 or anything under 6). Without the ORDER BY part of the
> : query, I only get 3 rows returned but, of course, they are not the

latest

> : dated one.
> :
> : Any idea how I get around this one, please?
> :
> : Thank you.
> :
> :
>
>

[Unregistered]

Hi Guys,

I had the same problem and I splitted the request in two parts. In your case, you should have something like that:

SELECT TOP 3 ID
FROM tblNews
WHERE (ID IN (SELECT ID
FROM tblNews
ORDER BY StoryDate DESC))

I hope that'll solve your problem.
Buy