4

Assume I have the following data:

| f1 | f2 | f3 |
|----|----|----|
|  1 |  1 |  1 |
|  1 |  1 |  5 |
|  1 |  2 |  3 |
|  1 |  2 |  6 |
|  1 |  3 |  4 |
|  1 |  3 |  7 |
|  2 |  1 |  2 |
|  2 |  1 | 22 |
|  2 |  2 |  3 |
|  2 |  2 |  4 |

There are two f3 values maximum per each f1,f2 combination.

For this specific example, I want to get top-2 minimum values per f1 and range (f2 - max(f2) per f1).

Example output:

| f1 | RNG| f3 |
|----|----|----|
|  1 |1 -3|  1 |
|  1 |1 -3|  3 |
|  1 |2 -3|  3 |
|  1 |2 -3|  4 |
|  1 |3 -3|  4 |
|  1 |3 -3|  7 |
|  2 |1- 2|  2 |
|  2 |1- 2|  3 |
|  2 |2- 2|  3 |
|  2 |2- 2|  4 |

To create the field RNG is not required. I only added it to show that for f1=1, there are 3 ranges: 1-3, 2-3, 3-3 created by the distinct values of f2 for f1=1. For each such range I want to calculate the top-k minimum values per f1 and range.

SQL Fiddle here:

http://sqlfiddle.com/#!15/9ddbb/1

Building the Ranges may be done by:

SELECT DISTINCT s1.f1,s1.f2 AS range_from ,s2.f2 AS range_to
FROM dbTable s1,
(SELECT f1,MAX(f2) AS f2 FROM dbTable
GROUP BY f1) s2
WHERE s1.f1=s2.f1
ORDER BY s1.f1,s1.f2;

Is there any way to achieve that, without building intermediate DB tables?

  • What should happen if there are ties? – ypercubeᵀᴹ Sep 1 '15 at 9:16
  • @ypercube Ties should be no problem, since I only want the upper two even if the third one is the same as the 2nd. Row_number() should have no problem. – Alexandros Sep 1 '15 at 9:24
  • I mean, what if there are rows with (1,1,3), (1,1,4), (1,2,3), .... Should the two 3 appear (1 |1 -3| 3 and 1 |1 -3| 3) or one 3 and one 4 (1 |1 -3| 3 and 1 |1 -3| 4)? – ypercubeᵀᴹ Sep 1 '15 at 9:41
  • @ypercube Thanks for clarifying that crucial detail. They should appear as two 3 and not 3,4. – Alexandros Sep 1 '15 at 9:46
6

Another way, using the LATERAL syntax, available from 9.3+ versions::

WITH t AS
  ( SELECT f1, f2,  
           MAX(f2) OVER (PARTITION BY f1) AS range_to
    FROM dbTable 
    GROUP BY f1, f2
  ) 
SELECT t.f1, 
       -- t.f2 AS range_from, t.range_to,
       t.f2 || ' - ' || t.range_to AS RNG,
       x.f3
FROM t
  CROSS JOIN LATERAL
    ( SELECT f3 
      FROM dbTable 
      WHERE f1 = t.f1 AND f2 >= t.f2 
      ORDER BY f3 LIMIT 2
    ) AS x (f3)
ORDER BY t.f1, t.f2, x.f3 ;

Test at SQLfiddle.

Or without the CTE:

SELECT t.f1, 
       -- t.f2 AS range_from, 
       -- MAX(t.f2) OVER (PARTITION BY t.f1) AS range_to,
       t.f2 || ' - ' || MAX(t.f2) OVER (PARTITION BY t.f1) AS RNG,
       x.f3
FROM dbTable AS t
  CROSS JOIN LATERAL
    ( SELECT f3 
      FROM dbTable 
      WHERE f1 = t.f1 AND f2 >= t.f2 
      ORDER BY f3 LIMIT 2
    ) AS x (f3)
GROUP BY t.f1, t.f2, x.f3
ORDER BY t.f1, t.f2, x.f3 ;
  • Upvoted and accepted since it is more elegant and advanced than my brute-force approach. – Alexandros Sep 2 '15 at 5:41
2

I think this query is correct:

SELECT f1,range_from,range_to,f3 FROM
(
/* s53 subquery joins original table and created ranges */
SELECT s.f1,s3.range_from,s3.range_to,s.f3, row_number() over (partition BY s.f1,s3.range_from ORDER BY f3) AS counter 
FROM dbTable s,
(
/* Created ranges are the s3 subquery */
SELECT DISTINCT s1.f1,s1.f2 AS range_from ,s2.f2 AS range_to
FROM dbTable s1,
(SELECT f1,MAX(f2) AS f2 FROM dbTable
GROUP BY f1) s2
WHERE s1.f1=s2.f1
ORDER BY s1.f1,s1.f2
) s3

WHERE s.f1=s3.f1 
AND (s.f2 >= s3.range_from AND s.f2<= s3.range_to)
ORDER BY s.f1,s3.range_from,s.f3
) s53
WHERE counter<=2
ORDER BY f1,range_from,range_to, counter;

And seems to give the correct output in SQL Fiddle :

| f1 | range_from | range_to | f3 |
|----|------------|----------|----|
|  1 |          1 |        3 |  1 |
|  1 |          1 |        3 |  3 |
|  1 |          2 |        3 |  3 |
|  1 |          2 |        3 |  4 |
|  1 |          3 |        3 |  4 |
|  1 |          3 |        3 |  7 |
|  2 |          1 |        2 |  2 |
|  2 |          1 |        2 |  3 |
|  2 |          2 |        2 |  3 |
|  2 |          2 |        2 |  4 |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.