sorting twice?!

[Tim]

here's a good one for you...

I want to return the last 20 records I have modified. I have a
datemodified field - excellent.
So I run a query to select the top 20 when ordered by datemodified
desc.
But now I have these results I want them sorted by companyname.

Is this possible?

Yes I could use my GUI to do the second sort, but can it be done just
in a query?

Thanks

Tim

[Jack Vamvas]

Re: sorting twice?!

Yes. Something like : SELECT myColID FROM myTable
WHERE myColID IN (SELECT TOP 20 FROM myTable ORDER BY dateDESC)
ORDER BY companyName ASC

--
----
Jack Vamvas
_______________ _______________ _____
Receive free SQL tips - www.ciquery.com/sqlserver.htm
_______________ _______________ _____


"Tim" <Citizen10Bears @gmail.com> wrote in message
news:1151401355 .662526.118990@ 75g2000cwc.goog legroups.com...[color=blue]
> here's a good one for you...
>
> I want to return the last 20 records I have modified. I have a
> datemodified field - excellent.
> So I run a query to select the top 20 when ordered by datemodified
> desc.
> But now I have these results I want them sorted by companyname.
>
> Is this possible?
>
> Yes I could use my GUI to do the second sort, but can it be done just
> in a query?
>
> Thanks
>
> Tim
>[/color]

[Erland Sommarskog]

Re: sorting twice?!

Tim (Citizen10Bears @gmail.com) writes:[color=blue]
> here's a good one for you...
>
> I want to return the last 20 records I have modified. I have a
> datemodified field - excellent.
> So I run a query to select the top 20 when ordered by datemodified
> desc.
> But now I have these results I want them sorted by companyname.
>
> Is this possible?[/color]

Sure. With derived tables (almost) everything is possible:

SELECT ...
FROM (SELECT TOP 20 ...
FROM tbl
ORDER BY datemodified DESC) AS x
ORDER BY companyname

A derived table is a verital temp table within the query so speak, and
is an immensly powerful tool to build complex queries. Important to know
is that the optimizer is very good at recasting computation order to get
better performance. (Although in this particular case it is not likely
to happen because of the TOP operator.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Tim]

Re: sorting twice?!

thanks guys.

Tim

Erland Sommarskog wrote:[color=blue]
> Tim (Citizen10Bears @gmail.com) writes:[color=green]
> > here's a good one for you...
> >
> > I want to return the last 20 records I have modified. I have a
> > datemodified field - excellent.
> > So I run a query to select the top 20 when ordered by datemodified
> > desc.
> > But now I have these results I want them sorted by companyname.
> >
> > Is this possible?[/color]
>
> Sure. With derived tables (almost) everything is possible:
>
> SELECT ...
> FROM (SELECT TOP 20 ...
> FROM tbl
> ORDER BY datemodified DESC) AS x
> ORDER BY companyname
>
> A derived table is a verital temp table within the query so speak, and
> is an immensly powerful tool to build complex queries. Important to know
> is that the optimizer is very good at recasting computation order to get
> better performance. (Although in this particular case it is not likely
> to happen because of the TOP operator.)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx[/color]