March 27, 2008 at 1:55 pm
I have a sorting dilemma.
How can I sort a column based on the data values in a column
and not alphabetically using order by?
create table [dbo].[test1] (
[Inv_Num] [int] NOT NULL ,
[Priority] [varchar] (10) NOT NULL
)
GO
insert into [dbo].test1 (inv_num, priority) values (400, 'high')
insert into [dbo].test1 (inv_num, priority) values (400, 'high')
insert into [dbo].test1 (inv_num, priority) values (400, 'high')
insert into [dbo].test1 (inv_num, priority) values (400, 'low')
insert into [dbo].test1 (inv_num, priority) values (400, 'low')
insert into [dbo].test1 (inv_num, priority) values (400, 'medium')
insert into [dbo].test1 (inv_num, priority) values (500, 'medium')
insert into [dbo].test1 (inv_num, priority) values (500, 'high')
GO
select * from dbo.test1
order by inv_num, priority
When I execute 'select * from dbo.test1' the results are returned to me based on the order they were inserted into the table.
When I execute 'select * from dbo.test1 order by inv_num, priority' the results are returned first by inv_num and then by priority... but priority is alphabetical, so it returns high, low, then medium.
What I want are rows returned to me first by inv_num and then by priority in the order of high, medium, low.
I have no control over the insertion of the data.
Thanks!!
March 27, 2008 at 1:58 pm
You can do this:
Order By
Case
When Priority = 'High' then 0
When Priority = 'Medium' then 1
When Priority = 'Low' then 2
End
That should get you what you need. Might be better to create a table with the priority values and a "SortBy" column, join to that table and then Order By that column. You'll get better performance that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply