Re: sorting twice?!

thanks guys.

Tim

Erland Sommarskog wrote:
Tim (Citizen10Bears@xxxxxxxxx) writes:
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?

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@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: Very slow query
    ... with lots if information in Books Online, Books, Courses, the Net etc. ... While testing the query in QA, use the "Show Estimated Query Plan" feature. ... This is what I suggest you also pick up using Profiler. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... What you said was what I first planned to say, but then I read the Books Online which implies that the batch is indeed reused, so I held back from saying that. ... the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ...
    (microsoft.public.sqlserver.tools)
  • Re: Many to one Select
    ... It still possible to define a query that has maximum of columns needed, ... INSERT statements with sample data. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: timezones in select statement
    ... > end, in the sense that I don't want it to be pulled back in the query, ... INSERT statements with sample data. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: UPDATE query gives Incorrect Syntax error
    ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ... No - no triggers on the table. ... Is it possible that a previous query has somehow become trapped and is ...
    (comp.databases.ms-sqlserver)