How to reverse a reverse order by?

11 Nov 2005 11:24 AM

Morten Wennevik

Hi,

using the statement

SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC

I get the last 10 rows in the Import table as expected, but how to I reverse these last 10 rows?

Morten

11 Nov 2005 11:31 AM

Tibor Karaszi

Use an outer query which has an ORDER BY in the other direction.

SELECT ...
FROM
(
SELECT TOP 10 ... FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
) AS i
ORDER BY rowID ASC

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Morten Wennevik" <Morten.Wenne***@email.adr> wrote in message
news:op.sz2pzfp2g1d8xu@tr023.bouvet.no...
>
> Hi,
>
> using the statement
>
> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
>
> I get the last 10 rows in the Import table as expected, but how to I reverse these last 10 rows?
>
> Morten

11 Nov 2005 11:38 AM

Morten Wennevik

Well, I tried this before, but I get an error near the keyword 'ORDER'

SELECT * FROM
(
SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
)
ORDER BY RowID ASC

Morten

On Fri, 11 Nov 2005 12:31:40 +0100, Tibor Karaszi <tibor_please.no.email_kara***@hotmail.nomail.com> wrote:

Show quote

> Use an outer query which has an ORDER BY in the other direction.
>
> SELECT ...
> FROM
> (
> SELECT TOP 10 ... FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
> ) AS i
> ORDER BY rowID ASC

11 Nov 2005 11:40 AM

Morten Wennevik

It accepts the query when I add AS i after (), but it still does not ascend the RowIDs

On Fri, 11 Nov 2005 12:38:16 +0100, Morten Wennevik <Morten.Wenne***@email.adr> wrote:

Show quote

> Well, I tried this before, but I get an error near the keyword 'ORDER'
>
> SELECT * FROM
> (
> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
> )
> ORDER BY RowID ASC
>
> Morten
>
>
>
> On Fri, 11 Nov 2005 12:31:40 +0100, Tibor Karaszi <tibor_please.no.email_kara***@hotmail.nomail.com> wrote:
>
>> Use an outer query which has an ORDER BY in the other direction.
>>
>> SELECT ...
>> FROM
>> (
>> SELECT TOP 10 ... FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
>> ) AS i
>> ORDER BY rowID ASC
>
>

11 Nov 2005 11:44 AM

Tibor Karaszi

You need to name the derived (inner) table, see the AS clause after the closing bracket.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Morten Wennevik" <Morten.Wenne***@email.adr> wrote in message
news:op.sz2ql2d7g1d8xu@tr023.bouvet.no...
> Well, I tried this before, but I get an error near the keyword 'ORDER'
>
> SELECT * FROM
> (
> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
> )
> ORDER BY RowID ASC
>
> Morten
>
>
>
> On Fri, 11 Nov 2005 12:31:40 +0100, Tibor Karaszi
> <tibor_please.no.email_kara***@hotmail.nomail.com> wrote:
>
>> Use an outer query which has an ORDER BY in the other direction.
>>
>> SELECT ...
>> FROM
>> (
>> SELECT TOP 10 ... FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
>> ) AS i
>> ORDER BY rowID ASC
>

11 Nov 2005 11:37 AM

Jens

Hi Morten,

hope I undertstand you right to reorder the queried rows before.

SELECT
* FROM
(
SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
) SubQuery
Order by RowID

So you want the hightest 10 Rows, but those in an ascending order,
right ?

HTH, Jens Suessmeyer.

11 Nov 2005 11:41 AM

Morten Wennevik

Yes, given RowIDs from 1-499 I want 490-499

On Fri, 11 Nov 2005 12:37:11 +0100, Jens <J***@sqlserver2005.de> wrote:

Show quote

> Hi Morten,
>
> hope I undertstand you right to reorder the queried rows before.
>
> SELECT
> * FROM
> (
> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
> ) SubQuery
> Order by RowID
>
> So you want the hightest 10 Rows, but those in an ascending order,
> right ?
>
> HTH, Jens Suessmeyer.
>
>

11 Nov 2005 11:47 AM

Morten Wennevik

Using your suggestion the RowIDs still got from 499-490, which is the opposite of what I want

On Fri, 11 Nov 2005 12:41:41 +0100, Morten Wennevik <Morten.Wenne***@email.adr> wrote:

Show quote

> Yes, given RowIDs from 1-499 I want 490-499
>
>
> On Fri, 11 Nov 2005 12:37:11 +0100, Jens <J***@sqlserver2005.de> wrote:
>
>> Hi Morten,
>>
>> hope I undertstand you right to reorder the queried rows before.
>>
>> SELECT
>> * FROM
>> (
>> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
>> ) SubQuery
>> Order by RowID
>>
>> So you want the hightest 10 Rows, but those in an ascending order,
>> right ?
>>
>> HTH, Jens Suessmeyer.
>>
>>
>
>

11 Nov 2005 11:51 AM

Morten Wennevik

Ok, I'm baffled, selecting * ignores the last reverse ordering, but selecting just RowID will give me 490-499

???

On Fri, 11 Nov 2005 12:47:07 +0100, Morten Wennevik <Morten.Wenne***@email.adr> wrote:

Show quote

> Using your suggestion the RowIDs still got from 499-490, which is the opposite of what I want
>
> On Fri, 11 Nov 2005 12:41:41 +0100, Morten Wennevik <Morten.Wenne***@email.adr> wrote:
>
>> Yes, given RowIDs from 1-499 I want 490-499
>>
>>
>> On Fri, 11 Nov 2005 12:37:11 +0100, Jens <J***@sqlserver2005.de> wrote:
>>
>>> Hi Morten,
>>>
>>> hope I undertstand you right to reorder the queried rows before.
>>>
>>> SELECT
>>> * FROM
>>> (
>>> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC
>>> ) SubQuery
>>> Order by RowID
>>>
>>> So you want the hightest 10 Rows, but those in an ascending order,
>>> right ?
>>>
>>> HTH, Jens Suessmeyer.
>>>
>>>
>>
>>
>
>