SQL query with ORDER BY Part 2

Question

This is the next question: SQL query with ORDER BY

But I think the SQL logic will be completely different, so I am posting it as a separate issue.

I am trying to expand my sql query SELECTand have some problems:

I have a table:

id    type      radius
-------------------------
1     type1     0.25
2     type2     0.59
3     type1     0.26
4     type1     0.78
5     type3     0.12
6     type2     0.45
7     type3     0.22
8     type3     0.98

and I'm trying to find out how the SELECT second smallest radius for each given type . Therefore, the returned recordset should look like this:

id    type      radius
-------------------------
3     type1     0.26
2     type2     0.59
7     type3     0.22

(Note: in the question I was looking for the lowest radius, not the second lowest radius).

I suppose I need to use LIMITand OFFSET, but if I use MIN(), it will not return a separate record containing the minimum radius?

- , ?

,

Answer 1

2-

SELECT  *
FROM    `test`.`rads`
WHERE   type = 'type wanted'
ORDER BY `radius` ASC
LIMIT 1, 1

, fetche ,

SELECT  id, type, radius
FROM    `test`.`rads` t
WHERE   id = (
    SELECT  id
    FROM    `test`.`rads` ti
    WHERE   ti.type = t.type
    ORDER BY `radius` ASC
    LIMIT 1, 1)
ORDER BY radius ASC, id DESC

, LIMIT

Answer 2

, , , :

SELECT *
FROM (
  SELECT id, 
         type,
         radius, 
         dense_rank() OVER (PARTITION BY type ORDER BY radius ASC) as radius_rank
  FROM radius_table
) t
WHERE radius_rank = 2

3- 14- , WHERE

, , ( LIMIT )

Answer 3

I would use the SQL query from your previous answer and add a WHERE setting to it, deleting all records containing the "id" of the corresponding "first lower radius".

SELECT t1.id,t1.type,t1.radius FROM table t1 
WHERE radius = ( 
     SELECT MIN(radius) FROM table
     WHERE radius = t1.radius
     AND id not IN (
       SELECT t2.id FROM table t2
       WHERE radius = ( 
         SELECT MIN(radius) FROM table
         WHERE radius = t2.radius
       )
     )
   )