if object_id('[tb1]') is not null drop table [tb1]
go
create table tb1 (id int,numb int )
insert into tb1select 1,10 union all
select 2,11 union all
select 3,12 union all
select 4,13 union all
select 5,14 union all
select 6,15 union all
select 7,16 union all
select 8,17 union all
select 9,18 union all
select 10,19 goselect id ,numb from
(
select id ,numb,1 as s from tb1 where numb>=15
union all
select id ,numb,0 as s from tb1 where numb<15
)
t order by s desc, (case when s=1 then numb end) asc ,(case when s=0 then numb end) desc--id numb
--6 15
--7 16
--8 17
--9 18
--10 19
--5 14
--4 13
--3 12
--2 11
--1 10
go
create table tb1 (id int,numb int )
insert into tb1select 1,10 union all
select 2,11 union all
select 3,12 union all
select 4,13 union all
select 5,14 union all
select 6,15 union all
select 7,16 union all
select 8,17 union all
select 9,18 union all
select 10,19 goselect id ,numb from
(
select id ,numb,1 as s from tb1 where numb>=15
union all
select id ,numb,0 as s from tb1 where numb<15
)
t order by s desc, (case when s=1 then numb end) asc ,(case when s=0 then numb end) desc--id numb
--6 15
--7 16
--8 17
--9 18
--10 19
--5 14
--4 13
--3 12
--2 11
--1 10
USE tempdb
IF OBJECT_ID('[TB1]') IS NOT NULL DROP TABLE [TB1]
GO
CREATE TABLE TB1 (ID INT,NUMB INT )
INSERT INTO TB1
SELECT 1,10 UNION ALL
SELECT 2,11 UNION ALL
SELECT 3,12 UNION ALL
SELECT 4,13 UNION ALL
SELECT 5,14 UNION ALL
SELECT 6,15 UNION ALL
SELECT 7,16 UNION ALL
SELECT 8,17 UNION ALL
SELECT 9,18 UNION ALL
SELECT 10,19
GO
SELECT * FROM TB1
ORDER BY CASE WHEN NUMB>='15' THEN 1
ELSE 2 END ASC/*
ID NUMB
6 15
7 16
8 17
9 18
10 19
1 10
2 11
3 12
4 13
5 14
*/
order by case SIGN(15-numb) when 0 then -1 else SIGN(15-numb) end,SIGN(15-numb)*numb desc
IF OBJECT_ID('[TB1]') IS NOT NULL DROP TABLE [TB1]
GO
CREATE TABLE TB1 (ID INT,NUMB INT )
INSERT INTO TB1
SELECT 1,10 UNION ALL
SELECT 2,11 UNION ALL
SELECT 3,12 UNION ALL
SELECT 4,13 UNION ALL
SELECT 5,14 UNION ALL
SELECT 6,15 UNION ALL
SELECT 7,16 UNION ALL
SELECT 8,17 UNION ALL
SELECT 9,18 UNION ALL
SELECT 10,19
GO
SELECT * FROM TB1
ORDER BY CASE WHEN NUMB>='15' THEN 1
ELSE 2 END ,NUMB DESC
/*
-------
ID NUMB
10 19
9 18
8 17
7 16
6 15
5 14
4 13
3 12
2 11
1 10*/