Help Ordering Result Set
-
I am having trouble trying to properly order a query result set. In its simplest form there are 2 columns, "Date" and "Employer". Here is an example of how I want the result set to be ordered:
Date Employer
1968 Bill's Tires
1971 Bill's Tires
1968 Dell Computer
1973 Dell Computer
1975 Dell Computer
1970 Johnson Controls
1971 Johnson Controls
1972 Labtec
1973 Labtec
So what I'm trying to accomplish is to have the Employers sorted so they are grouped together and the earliest Date associated with each Employer put in ASC order (ie: 1968 Bill's Tires, 1968 Dell, 1970 Johnson, 1972 Labtec). If I use 'order by Date, Employer', it orders all dates regardless of Employer. If I use 'order by Employer, Date', it orders Employers correctly but the Dates aren't in ASC order outside of each Employer group.
How can I write a query to accomplish this ordered result?
-
Try "SELECT rtrim(Employer), date from table_name ORDER BY rtrim(Employer), date"
-
Maybe I am not understanding the requirements correctly, but ordering by employer & date should give you what you want...
could you please post the resultset that you "do" get when using this "order by" sequence ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks but, as I stated in my post, that doesn't accomplish the need to keep the dates in order outside of each Employer. That just orders the Employers, and the dates WITHIN each Employer. Review my post again for more detail, and thanks.
-
This is what you posted:
>>Here is an example of how I want the result set to be ordered:
>>Date Employer
>>1968 Bill's Tires
>>1971 Bill's Tires
>>1968 Dell Computer
You clearly show date sorted within each employer, otherwise Dell's 1968 would not be coming after Bill's 1971.
You need to clearly state your requirements and/or give accurate sample data.
-
Sorry Ed - I've stared & stared at "Here is an example of how I want the result set to be ordered:" and still don't understand...
could you please explain again...maybe it's my being "singularly obtuse" day..
**ASCII stupid question, get a stupid ANSI !!!** -
unless you want something like this..
select min(date) as date, employer from myTable
group by employer
?!?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Sushila: Perhaps it is my error in not fabricating the data properly. Here is the revised data & also the results set when I order by Employer, Date:
Date Employer
1968 Bill's Tires
1971 Bill's Tires
1972 Dell Computer
1973 Dell Computer
1975 Dell Computer
1970 Johnson Controls
1971 Johnson Controls
1968 Labtec
1970 Labtec
Here is the result set I want:
Date Employer
1968 Bill's Tires
1971 Bill's Tires
1968 Labtec
1970 Labtec
1970 Johnson Controls
1971 Johnson Controls
1972 Dell Computer
1973 Dell Computer
1975 Dell Computer
Notice that the Employers are grouped together, but only by their earliest date (ie: Labtec comes after Bill's Tires b/c it's earliest date is 1968 and so is Bill's. Johnson comes next b/c its earliest date is 1970).
-
Sorry, PW. See my last post.
-
Select Date, t.Employer
From #t As t
Inner Join
(
Select Employer, Min(Date) As SortValue
From #t
Group by Employer
) dtSorted
On ( t.Employer = dtSorted.Employer )
Order By dtSorted.SortValue, t.Employer, t.Date
[Edit] Fixed a missing group by
-
Thanks for your help, PW. I think that did the trick.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply