Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable AS
SELECT id, n1, 1000* random() as h, n1 as n2, 1000* random() as l
FROM generate_series(1,3) id
cross join generate_series(1,15) n1

SELECT 45


SELECT id, 
       min(h) as min_h,
       max(h) as max_h,
       max( case when low_rn = 1 then n1 end) as n1_for_min,
       min( case when high_rn = 1 then n1 end) as n1_for_max
FROM (
  SELECT *,
        row_number() over (partition by id order by h) as low_rn,
        row_number() over (partition by id order by h desc) as high_rn
  FROM mytable
) x 
WHERE 1 IN (low_rn, high_rn)
GROUP BY id


id
min_h
max_h
n1_for_min
n1_for_max


1
17.6611808128655
845.042669214308
8
12

2
63.54466592893
996.056572068483
9
15

3
45.6339530646801
875.932836439461
3
7



SELECT 3


SELECT *
FROM (
  SELECT *,
        row_number() over (partition by id order by h) as low_rn,
        row_number() over (partition by id order by h desc) as high_rn
  FROM mytable
) x 
WHERE 1 IN (low_rn, high_rn)


id
n1
h
n2
l
low_rn
high_rn


1
12
845.042669214308
12
463.137058075517
15
1

1
8
17.6611808128655
8
643.120862077922
1
15

2
15
996.056572068483
15
497.513848356903
15
1

2
9
63.54466592893
9
373.333103489131
1
15

3
7
875.932836439461
7
374.800697900355
15
1

3
3
45.6339530646801
3
171.945017762482
1
15



SELECT 6

id	min_h	max_h	n1_for_min	n1_for_max
1	17.6611808128655	845.042669214308	8	12
2	63.54466592893	996.056572068483	9	15
3	45.6339530646801	875.932836439461	3	7

id	n1	h	n2	l	low_rn	high_rn
1	12	845.042669214308	12	463.137058075517	15	1
1	8	17.6611808128655	8	643.120862077922	1	15
2	15	996.056572068483	15	497.513848356903	15	1
2	9	63.54466592893	9	373.333103489131	1	15
3	7	875.932836439461	7	374.800697900355	15	1
3	3	45.6339530646801	3	171.945017762482	1	15