Complicated Sort
-
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
-
How about:
SELECT *
FROM YourTable
ORDER BY
CASE Code
WHEN 1 THEN 10000 -- larger than MAX(ACCT)
ELSE Acct
END
,Amount DESC
-
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
-
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
-
SELECT *
FROM YourTable
ORDER BY
CASE Code
WHEN 1 THEN 'ZZZZZ' -- larger than MAX(ACCT)
ELSE Acct
END
,Amount DESC
-
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 -
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.
-
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 -
-- 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"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply