How to find the smallest/largest value!

jak1984

The result is this:
1st largest / 1st smallest 90/1
2nd largest / 2nd smallest 23/3
3rd largest / 3rd smallest 22/4
4th largest / 4th smallest 12/5
5th largest / 5th smallest 8/8
6th largest / 6th smallest 5/12
7th largest/7th smallest 4/22
8th largest / 8th smallest 3/23
9th largest / 9th smallest 1/90
If it is a format that requires the original poster, it needs to be modified.

shadowhele

if object_id('ta')>0 drop table ta

create table ta (id varchar(8),num int)

insert into ta select '01', 1
insert into ta select '02', 3
insert into ta select '03', 2
insert into ta select '04', 5
insert into ta select '05', 18
insert into ta select '06', 99
insert into ta select '07', 19
insert into ta select '08', 29
insert into ta select '09', 39
insert into ta select '10', 49

insert into #t1 select * from ta
create table #ta(string1 varchar(20),string2 varchar(20))
declare @max int --Always get the maximum value
declare @min int --Always get the minimum value
declare @i int --time
set @max=(select max(num) from #t1)
set @min=(select min(num) from #t1)
set @i=0
select * from #t1 where num in(select max(num) from #t1) union all select * from #t1 where num in(select min(num) from #t1)
while @@rowcount>0
begin
set @i=@i+1
insert into #ta select'most'+cast(@i as varchar)+'小/最'+cast(@i as varchar)+'big',cast(@min as varchar)+'/'+cast(@ max as varchar)
delete from #t1 where id in(select id from #t1 where num in(select max(num) from #t1)) ​​or id in(select id from #t1 where num in(select min(num) from #t1))
set @max=(select max(num) from #t1)
set @min=(select min(num) from #t1)
select * from #t1 where num in(select max(num) from #t1) union all select * from #t1 where num in(select min(num) from #t1)
end

select * from #ta
/*result
string1 string2
-------------------- --------------------
1 small / 1 large 1/99
2 Smallest / 2 Largest 2/49
3 Smallest / 3 Largest 3/39
4 Smallest / 4 Largest 5/29
The 5th smallest/the 5th largest 18/19

(5 rows affected)
*/

drop table ta