How to find the smallest/largest value!

karaxebury

The table is as follows:
id num
01 1
02 3
03 3
04 5
05 18
06 99
.. ..

num may be repeated, and the maximum id is 10.

begging
Min/Max 1/99
Second smallest / second largest 3/18
Third smallest/third largest 5
Fourth smallest/fourth largest
Fifth smallest / fifth largest

wgfxman

--The fifth small (large)
select top 1 * from (select top 5 * from form ORDER BY num (DESC)) m

. . . 1\2\.. the same

karaxebury

ID number is not fixed

michealhenry

--Build a table
create table tab
(
id varchar(2),
num int
)

insert into tab select '01', 1
insert into tab select '02', 3
insert into tab select '03', 3
insert into tab select '04', 5
insert into tab select '05', 18
insert into tab select '06', 99

-Create an auxiliary table
select * into info
from
(
select 1 as num,'一' as cnum union
select 2,'two' union
select 3,'three' union
select 4,'four' union
select 5,'five' union
select 6,'six' union
select 7,'seven' union
select 8,'eight' union
select 9,'nine' union
select 10,'ten'
)tt

--Statement
select
'Th' + c.cnum +'small/th' + c.cnum +'big' as name,
case when a.id <(a.cou +1)/2 then cast(a.num as varchar) +'/'+cast(b.num as varchar)
     when a.id = (a.cou +1)/2 then cast(a.num as varchar)
     else'' end as value
from
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)a inner join
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)b on a.id = b.cou + 1-b.id
inner join info c on a.id = c.num

--result
First small / first largest 1/99
Second smallest / second largest 3/18
Third smallest/third largest 5
Fourth smallest/fourth largest
Fifth smallest / fifth largest

michealhenry

--edit a bit


--Build a table
create table tab
(
id varchar(2),
num int
)

insert into tab select '01', 1
insert into tab select '02', 3
insert into tab select '03', 3
insert into tab select '04', 5
insert into tab select '05', 18
insert into tab select '06', 99

select * into info
from
(
select 1 as num,'most' as cnum union
select 2,'second' union
select 3,'third' union
select 4,'fourth' union
select 5,'fifth' union
select 6,'sixth' union
select 7,'seventh' union
select 8,'eighth' union
select 9,'ninth' union
select 10,'tenth'
)tt

--Statement
select
c.cnum +'small/'+ c.cnum +'big' as name,
case when a.id <(a.cou +1)/2 then cast(a.num as varchar) +'/'+cast(b.num as varchar)
     when a.id = (a.cou +1)/2 then cast(a.num as varchar)
     else'' end as value
from
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)a inner join
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)b on a.id = b.cou + 1-b.id
inner join info c on a.id = c.num

--result
Min/Max 1/99
Second smallest / second largest 3/18
Third smallest/third largest 5
Fourth smallest/fourth largest
Fifth smallest / fifth largest

michealhenry

Min/Max 1/99
Second smallest / second largest 3/18
Third smallest/third largest 5
Fourth smallest/fourth largest
Fifth smallest / fifth largest

3107869

Use the following data
insert into tab select '01', 1
insert into tab select '02', 3
insert into tab select '03', 2
insert into tab select '04', 5
insert into tab select '05', 18
insert into tab select '06', 99
insert into tab select '07', 19
insert into tab select '08', 29
insert into tab select '09', 39
insert into tab select '10', 49
The results are:
Min/Max 1/99
Second smallest / second largest 2/49
Third smallest/third largest 3/39
Fourth smallest/fourth largest 5/29
Fifth smallest/fifth largest 18
Sixth smallest/sixth largest
Seventh smallest/seventh largest
Eighth small/eighth largest
Ninth Small/Ninth Large
Tenth Smallest/Tenth Largest

Missing a 19

michealhenry

--There is indeed a problem

--Build a table
create table tab
(
id varchar(2),
num int
)

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

--Modified statement
select
c.cnum +'small/'+ c.cnum +'big' as name,
case when a.id <(a.cou + 1)*1.0/2 then cast(a.num as varchar) +'/'+cast(b.num as varchar)
     when a.id = (a.cou + 1)*1.0/2 then cast(a.num as varchar)
     else'' end as value
from
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)a inner join
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)b on a.id = b.cou + 1-b.id
inner join info c on a.id = c.num

--result
name value
------------- ------------------------------------- ------------------------
Min/Max 1/99
Second smallest / second largest 2/49
Third smallest/third largest 3/39
Fourth smallest/fourth largest 5/29
Fifth Smallest/Fifth Largest 18/19
Sixth smallest/sixth largest
Seventh smallest/seventh largest
Eighth small/eighth largest
Ninth Small/Ninth Large
Tenth Smallest/Tenth Largest

(The number of rows affected is 10 rows)


--===================================
--Re-test the previous data
--===================================
--Build table drop table tab
create table tab
(
id varchar(2),
num int
)

insert into tab select '01', 1
insert into tab select '02', 3
insert into tab select '03', 3
insert into tab select '04', 5
insert into tab select '05', 18
insert into tab select '06', 99

select
c.cnum +'small/'+ c.cnum +'big' as name,
case when a.id <(a.cou + 1)*1.0/2 then cast(a.num as varchar) +'/'+cast(b.num as varchar)
     when a.id = (a.cou + 1)*1.0/2 then cast(a.num as varchar)
     else'' end as value
from
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)a inner join
(
select distinct id = (select count(distinct num) from tab where num <= a.num),
cou = (select count(distinct num) from tab),
num
from tab a
)b on a.id = b.cou + 1-b.id
inner join info c on a.id = c.num

--result
name value
------------- ------------------------------------- ------------------------
Min/Max 1/99
Second smallest / second largest 3/18
Third smallest/third largest 5
Fourth smallest/fourth largest
Fifth smallest / fifth largest

(The number of rows affected is 5 rows)

mill1128

Master
I can't understand it haha

jak1984

select '01' as id, 1 as num into #ls
union all select '02' as id,3
union all select '03' as id,4
union all select '04' as id,5
union all select '05' as id,8
union all select '06' as id,12
union all select '07' as id,22
union all select '08' as id,23
union all select '09' as id,90

select distinct'section'+cast(z.mc as varchar(10))+'big/section'+cast(z.mc as varchar(10))+'small' as mc,
cast(z.num as varchar(10))+'/'+cast(m.num as varchar(10)) as value
from
(select (select count(distinct num) from #ls where num>=a.num)as mc,a.num from #ls a) z
inner join
(select (select count(distinct num) from #ls where num<=a.num)as mc,a.num from #ls a) m on z.mc=m.mc
order by mc

drop table #ls

I wrote one, I don’t know if it is the case.