Complicated Sort

Question

Post reply

Complicated Sort

texpic

SSCertifiable

Points: 5887
More actions
February 9, 2007 at 10:06 am

#90949

I can't figure this one out, thanks!!!
If Code > 1, sort these first by Acct, Amount
THEN
If Code = 1, sort remaining by Amount
Before Sort:
Acct Amount Code
1    100    1
2    200    1
3    200    2
3    900    2
4    400    3
4    500    3
4    600    3
5    900    1
6    800    1
After Sort:
Acct Amount Code
3    900    2
3    200    2
4    600    3
4    500    3
4    400    3
5    900    1
6    800    1
2    200    1
1    100    1

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

How about:

SELECT *

FROM YourTable

ORDER BY

CASE Code

WHEN 1 THEN 10000 -- larger than MAX(ACCT)

ELSE Acct

END

,Amount DESC

texpic SSCertifiable Points: 5887 More actions · Answer 2

Actually these account numbers have alphas in

them. I didn't realize there could be a case

in a sort, cool.

This code below does not work, but this is

what I'm after. Thanks again!!

SELECT *

From YourTable

ORDER BY

Case Code

WHEN > 1 THEN Acct, Amount

ELSE

Amount

END

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Try something like this:

create table dbo.MyTable (

Account varchar(10),

Amount money,

Code int

)

go

--Acct Amount Code

insert into dbo.MyTable(Account, Amount, Code) values ('1',100,1)

insert into dbo.MyTable(Account, Amount, Code) values ('2',200,1)

insert into dbo.MyTable(Account, Amount, Code) values ('3',200,2)

insert into dbo.MyTable(Account, Amount, Code) values ('3',900,2)

insert into dbo.MyTable(Account, Amount, Code) values ('4',400,3)

insert into dbo.MyTable(Account, Amount, Code) values ('4',500,3)

insert into dbo.MyTable(Account, Amount, Code) values ('4',600,3)

insert into dbo.MyTable(Account, Amount, Code) values ('5',900,1)

insert into dbo.MyTable(Account, Amount, Code) values ('6',800,1)

go

select * from dbo.MyTable

go

create table #MyTable (

SortKey int identity(1,1),

Account varchar(10),

Amount money,

Code int

)

insert into #MyTable (

Account,

Amount,

Code)

select

Account,

Amount,

Code

from

dbo.MyTable

where

Code > 1

order by

Account asc,

Amount desc

insert into #MyTable (

Account,

Amount,

Code)

select

Account,

Amount,

Code

from

dbo.MyTable

where

Code = 1

order by

Amount desc

select

Account,

Amount,

Code

from

#MyTable

order by

SortKey

drop table #MyTable

drop table dbo.MyTable

Ken McKelvey SSCoach Points: 18856 More actions · Answer 4

SELECT *

FROM YourTable

ORDER BY

CASE Code

WHEN 1 THEN 'ZZZZZ' -- larger than MAX(ACCT)

ELSE Acct

END

,Amount DESC

Rick Townsend Ten Centuries Points: 1228 More actions · Answer 5

Creating a sortkey is probably the best solution, but I imagine it would run faster as a union, rather than inserting into a temp table:

select Account, Amount, Code

from (select 1 as sortkey, *

from #MyTable

where code > 1

union all

select 2 as sortkey, *

from #MyTable

where code = 1) temp

order by sortkey, Account, Amount desc

(I used UNION ALL because I'm assuming any duplicates in the actual data are valid and should be returned.)

Rick
townsends.ca

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

The identity field of the temp table is the sortkey. The data is inserted in the required sort order for each sort requirement. The sort by the sort key after both inserts ensures that the data is output in the requested order.

Rick, your order by at the end of the union all query will still sort all records by account, if the code = 1, the sort is only by amount descending.

Rick Townsend Ten Centuries Points: 1228 More actions · Answer 7

Sorry, I guess I was a bit cryptic. I know the sortkey was the identity column - I used that technique for numbering results, until 2005 came out with the rownumber() function. I was saying your approach was good. (Also, in your script I changed the table dbo.MyTable to a temp table, as I don't like creating full-fledged tables in scripts when a locally scoped table will do. I've renamed it below to avoid the confusion.)

I didn't notice the lack of Account in the second sort criteria, but that's easily remedied using the same technique:

select

Account, Amount, Code

from (select 1 as sortkey, account as sortkey2, *

from #ATableWithTheTestData

where code > 1

union all

select

2 as sortkey, 0 as sortkey2, *

from #ATableWithTheTestData

where code = 1) temp

order by sortkey, sortkey2, Amount desc

Rick
townsends.ca

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

-- prepare sample data

declare @mytable table (account varchar(10), amount money, code int)

insert

@mytable

select '1', 100, 1 union all

select '2', 200, 1 union all

select '3', 200, 2 union all

select '3', 900, 2 union all

select '4', 400, 3 union all

select '4', 500, 3 union all

select '4', 600, 3 union all

select '5', 900, 1 union all

select '6', 800, 1

-- Show the result

SELECT Account,

Amount,

Code

FROM @MyTable

ORDER BY CASE

WHEN Code = 1 THEN 0xFFFF

ELSE Account

END,

Amount DESC

Same as shown before but includes binary values to handle different collations and so on...

N 56°04'39.16"
E 12°55'05.25"