SQL-min () gets the lowest value, max () the highest, what if I want the second (or fifth or nth) lowest value?

Question

The problem I'm trying to solve is that I have a table like this:

a and b refer to a point in another table. distance is the distance between points.

| id | a_id | b_id | distance | delete | | 1 | 1 | 1 | 1 | 0 | | 2 | 1 | 2 | 0.2345 | 0 | | 3 | 1 | 3 | 100 | 0 | | 4 | 2 | 1 | 1343.2 | 0 | | 5 | 2 | 2 | 0.45 | 0 | | 6 | 2 | 3 | 110 | 0 | .... 

The important column I'm looking for is a_id. If I wanted to save cabinet b for each a, I could do something like this:

 update mytable set delete = 1 from (select a_id, min(distance) as dist from table group by a_id) as x where a_gid = a_gid and distance > dist; delete from mytable where delete = 1; 

Which would give me a result table as follows:

 | id | a_id | b_id | distance | delete | | 1 | 1 | 1 | 1 | 0 | | 5 | 2 | 2 | 0.45 | 0 | .... 

i.e. I need one row for each a_id value, and this row should have the lowest distance value for each a_id.

However, I want to save the 10 closest points for each a_gid. I could do this with the plpgsql function, but I'm curious if there is a SQL-y way.

min () and max () return the smallest and largest value, if there is an aggregate function such as nth () that returns the nth largest / smallest value, then I could do it the same way as described above.

I am using PostgeSQL.

Answer 1

Try the following:

 SELECT * FROM ( SELECT a_id, ( SELECT b_id FROM mytable mib WHERE mib.a_id = ma.a_id ORDER BY dist DESC LIMIT 1 OFFSET s ) AS b_id FROM ( SELECT DISTINCT a_id FROM mytable mia ) ma, generate_series (1, 10) s ) ab WHERE b_id IS NOT NULL 

Tested on PostgreSQL 8.3

Answer 2

I like postgres, so it took second place when I saw this question.

So for the table:

  Table "pg_temp_29.foo" Column | Type | Modifiers --------+---------+----------- value | integer | 

With values:

  SELECT value FROM foo ORDER BY value; value ------- 0 1 2 3 4 5 6 7 8 9 14 20 32 (13 rows) 

You can do:

 SELECT value FROM foo ORDER BY value DESC LIMIT 1 OFFSET X 

Where X = 0 for the highest value, 1 for the second highest, 2 ... etc.

This can be optionally built into the subquery to get the required value. So, to use the dataset provided in the original question, we can get a_ids with the ten lowest distances by doing:

 SELECT a_id, distance FROM mytable WHERE id IN (SELECT id FROM mytable WHERE t1.a_id = t2.a_id ORDER BY distance LIMIT 10); ORDER BY a_id, distance; a_id | distance ------+---------- 1 | 0.2345 1 | 1 1 | 100 2 | 0.45 2 | 110 2 | 1342.2 

Answer 3

Does PostgreSQL have the rank of analytic function ()? If yes, try:

 select a_id, b_id, distance from ( select a_id, b_id, distance, rank() over (partition by a_id order by distance) rnk from mytable ) where rnk <= 10; 

Answer 4

This SQL should find you, that the lowest salary should work in SQL Server, MySQL, DB2, Oracle, Teradata and almost any other DBMS: (note: low performance due to sub-query)

 SELECT * /*This is the outer query part */ FROM mytable tbl1 WHERE (N-1) = ( /* Subquery starts here */ SELECT COUNT(DISTINCT(tbl2.distance)) FROM mytable tbl2 WHERE tbl2.distance < tbl1.distance) 

The most important thing to understand in the above query is that the subquery is evaluated every time a row is processed by an external query. In other words, the internal request cannot be processed independently of the external request, since the internal request also uses the value tbl1.

To find the Nth smallest value, we simply find a value that has exactly N-1 values ​​below itself.