473,324 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

Jun 27 '06 #1
3 2334
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" <Ci************@gmail.com> wrote in message
news:11**********************@75g2000cwc.googlegro ups.com...
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

Jun 27 '06 #2
Tim (Ci************@gmail.com) 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, es****@sommarskog.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
Jun 27 '06 #3
Tim
thanks guys.

Tim

Erland Sommarskog wrote:
Tim (Ci************@gmail.com) 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, es****@sommarskog.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


Jun 27 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Pratyush | last post by:
Hi, Suppose there is a vector of objects of class A, i.e., std::vector<A> vec_A(N); The class A satisifies all the STL vector requirements. Now I wish to add some attributes for each of the...
22
by: mike | last post by:
If I had a date in the format "01-Jan-05" it does not sort properly with my sort routine: function compareDate(a,b) { var date_a = new Date(a); var date_b = new Date(b); if (date_a < date_b)...
2
by: Aravind | last post by:
Hi folks. I have a form, frmHistory, which has 4 command buttons: Sort Title (cmdSortTitle), Sort Name (cmdSortName), Due Today (cmdDueToday), and Due List (cmdDueList). Sort Title and Sort...
3
by: melanieab | last post by:
Hi, I'm programatically sorting in a datagrid. When a column header is clicked, the sort happens twice for some reason, making it looks like it only sorts in descending order. I can tell it...
19
by: Owen T. Soroke | last post by:
Using VB.NET I have a ListView with several columns. Two columns contain integer values, while the remaining contain string values. I am confused as to how I would provide functionality to...
25
by: Dan Stromberg | last post by:
Hi folks. Python appears to have a good sort method, but when sorting array elements that are very large, and hence have very expensive compares, is there some sort of already-available sort...
1
by: Eric | last post by:
I have a GridView control that I want to sort on multiple columns when I click a particular column. For example, I display Last name + ", " + First name in the first column and display id in the...
16
by: Kittyhawk | last post by:
I would like to sort an Arraylist of objects on multiple properties. For instance, I have a Sort Index property and an ID property (both integers). So, the results of my sort would look like this:...
1
by: jjjoic | last post by:
Hi, I use Access 2003 to generate the back-end data for a ColdFusion report at work. The report is sorted by a column and based on the sorting, rankings are assigned to each row(i.e. the biggest...
7
by: abracadabra | last post by:
I am reading an old book - Programming Pearls 2nd edition recently. It says, "Even though the general C++ program uses 50 times the memory and CPU time of the specialized C program, it requires...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.