MS SQL Server - Order by clause help

Author

Order by clause help

accyboy1981

2006-02-28, 8:23 pm

Hi,

I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.

Thanks in advance
Simon

Tom Moreau

2006-02-28, 8:23 pm

Try:

select top 50
*
from
MyTable
order by
MyCol desc

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"accyboy1981" <accyboy1981@gmail.com> wrote in message
news:1141139037.380049.4520@j33g2000cwa.googlegroups.com...
Hi,

I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.

Thanks in advance
Simon

Hugo Kornelis

2006-02-28, 8:23 pm

On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:

>Hi,
>
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>
>Thanks in advance
>Simon

Hi Simon,

If I understand you correctly, then you need someting like

SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

Sponsored Links