Order by clause help

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
SimonTry:
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|||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

Order by clause help

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
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.googlegrou ps.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
|||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

Order by clause help

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
SimonTry:
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|||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