How to reverse a reverse order by?

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?
MortenUse 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/
"Morten Wennevik" <Morten.Wennevik@.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 rever
se these last 10 rows?
> Morten|||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.|||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_kar
aszi@.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|||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.Wennevik@.email.a
dr> wrote:

> 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_k
araszi@.hotmail.nomail.com> wrote:
>
>|||Yes, given RowIDs from 1-499 I want 490-499
On Fri, 11 Nov 2005 12:37:11 +0100, Jens <Jens@.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.
>|||You need to name the derived (inner) table, see the AS clause after the clos
ing bracket.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Morten Wennevik" <Morten.Wennevik@.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_karaszi@.hotmail.nomail.com> wrote:
>
>|||Using your suggestion the RowIDs still got from 499-490, which is the opposi
te of what I want
On Fri, 11 Nov 2005 12:41:41 +0100, Morten Wennevik <Morten.Wennevik@.email.a
dr> wrote:

> Yes, given RowIDs from 1-499 I want 490-499
>
> On Fri, 11 Nov 2005 12:37:11 +0100, Jens <Jens@.sqlserver2005.de> wrote:
>
>|||Ok, I'm baffled, selecting * ignores the last reverse ordering, but selectin
g just RowID will give me 490-499
?
On Fri, 11 Nov 2005 12:47:07 +0100, Morten Wennevik <Morten.Wennevik@.email.a
dr> wrote:

> Using your suggestion the RowIDs still got from 499-490, which is the oppo
site of what I want
> On Fri, 11 Nov 2005 12:41:41 +0100, Morten Wennevik <Morten.Wennevik@.email
.adr> wrote:
>
>

How to Return a subset of resultset of some stored proc

Hi,

I want to write a query to select some of the columns from result of stored proc,

My expected code will be like that, but its not allowed,

Select first_Name, Last_Name, Last_Used_date from (Execute sp_reportNumber15 '9555')

I have around 30 different stored proc but I know some of the columns are in resultset of every stored proc, So I want to write a generalize stored proc to whom I will just pass stored proc name and it will return me the subset of its result.

Thanks,

Imran.

Imran:

I would first try to convince you to take another approach. Reports -- especially heavy reports -- can be very resource intensive. And to take the results of some 30 different reports and the massage these results and take small subsets of each report and the presenting the results to an end user sounds like doing a lot of work to simplify the coding of a developer. To me this is putting the work in the wrong place.

When I implement a database one of my goals always is to provide service to each request as fast as possible. The implementation you propose does not aim at that goal. To me, the implementation you propose aims at taking a round-about path to gathering the data and hoping that the response to the request might be fast enough. This brings to me visions of dozens of tables being table scanned when the data from much fewer tables is needed. If it were my server I would view this as abuse.

Please, rethink this before you go forward.

Dave

|||

Hi Dave,

I have not written that I want to compile resultset of 30 reports at the same time. The scenario is like as,

One enduser want sometimes 5 specific columns from resultset of sp_report1, sometime he needs same 5 columns from resultset of sp_report2, in actual the number of columns returned in resultsets is different of sp_report1 and sp_report2 but same 5 columns are present in resultset of every sp, like sp_report1 return 30 columns, and sp_report4 returns 15 columns.

I want to write a new stored proc for him, say for example sp_getData 'sp_report1'. By this kind of stored proc he doesnt need to fetch full resultset of every sp, this will reduce network load also,

when user passes the parameter value as sp_report1, then I will return 5 columns from resultset of report1, and when user passes parameter value as 'sp_report15' then I will return 5 columns from resultset of report15

My question is only that, if you have a vision to write this kind of statement, conceptually my statement will be as,

Select Col1, Col4, Col6, Col7, Col9 from (Exec sp_getData @.repnumber)

but syntactically it is wrong, if there is some possibility then just reply otherwise dont waste your time as well as my time.

thanks,

Imran.

|||

convert your sp into function..

so you can do Select Col1, Col4, Col6, Col7, Col9 from fn_getData (@.repnumber) as Data

|||

Thank you so much ... :D

This is exactly what I want...

so nice of you