Need Help with Grouping and ordering

Question

Need Help with Grouping and ordering

Jay Green

Grasshopper

Points: 17
More actions
April 24, 2009 at 2:43 pm

#131522

Thanks for taking the time to look this over
I need to write a query that selects everything from a table and then orders the result base on a specific result.
For example. I have a field call Type.
the data in the field is as follows
TypeA
TypeB
TypeC
TypeD
I dont want them ordered in ascending or descending order
I want to list TypeC first, Then TypeA, Then TypeB, and lastly TypeD
so the first X number of rows in my results set would be records belonging to typeC and then when all the typeC are listed the result set lists TypeA records.
I hope I explained this well enough
Thanks for your help

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Jay Green (4/24/2009)
so the first X number of rows in my results set would be records belonging to typeC and then when all the typeC are listed the result set lists TypeA records.

Sorry, I don't really understand...

You wrote that the first X rows belong to TypeC. How do they belong to? Are there any other columns which specify the relation between other rows and TypeC?

Maybe have a look to the link in my signature to provide a bit more explaining information 😉

Thanks

Flo

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 2

Maybe something like this?

SELECT {columns}

FROM tables

WHERE {some filtering here}

ORDER BY CASE WHEN Type = 'TypeA' THEN 1

WHEN Type = 'TypeC' THEN 2

WHEN Type = 'TypeB' THEN 3

WHEN Type = 'TypeD' THEN 4

ELSE 5

END;

If you need further help, review the article I link to in my signature.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jay Green Grasshopper Points: 17 More actions · Answer 3

Jay Green

Grasshopper

Points: 17

April 24, 2009 at 5:29 pm

#983876

perfect

Thank you so much

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 4

Glad I could help - thank you for the feedback.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

One other thought, although I know and love the ORDER BY CASE technique...

If you will be using this sort order in many places, and may be adding additional items in the future, maintenance will be easier if you store the sort priority in a table (or a new column to an existing table). That way you can add new items or change the sort priority without having to identify every query, procedure, or function that relies on it.

declare @type table (xType Char(10), priority int)

insert into @type

select 'Type A', 1 union all

select 'Type B', 3 union all -- priority differs from alphabetical

select 'Type C', 2 union all

select 'Type D', 4

declare @sample table (xType Char(10), filler char(20))

insert into @sample

select 'Type A', 'Blah de blah blah' union all

select 'Type B', 'Dum de doo de dah' union all

select 'Type C', 'Nom Nom Nom' union all

select 'Type D', 'Fnord'

select * from @sample

--

select s.*

from @sample s

join @type t on t.xtype = s.xtype

order by t.priority

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills