SQL – Methods of Reformatting into Equivalent Forms 4

December 2, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

A couple of years ago someone asked in the comp.databases.oracle.misc Usenet group the following question:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/1ce4e2a5bfa5d86e

I have to limit the number of results without using ROWNUM or something like that. One hint was to use “JOIN”.

I have to select the 10 biggest persons from a table “persons”

id, firstname, lastname, size, age

WITHOUT using ROWNUM or “limitter” like this.

Do you have any idea?

This message thread, like several others, generated suggestions from several people.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I suggested the following:

Method #1:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    LAG(ID,1,-1) OVER (ORDER BY HEIGHT DESC) SIGNAL,
    HEIGHT END_HEIGHT,
    LEAD(HEIGHT,9,0) OVER (ORDER BY HEIGHT DESC) START_HEIGHT
  FROM
    T1) R,
  T1
WHERE
  R.SIGNAL=-1
  AND T1.HEIGHT BETWEEN R.START_HEIGHT AND R.END_HEIGHT;

Method #2:
SELECT
  T1.ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    ID,
    PERCENT_RANK() OVER (ORDER BY T1.HEIGHT DESC) POSITION,
    TR.TOTAL_ROWS
  FROM
    (SELECT
      COUNT(*) TOTAL_ROWS
    FROM
      T1) TR,
    T1) TR,
  T1
WHERE
  TR.POSITION<=(10/TR.TOTAL_ROWS)
  AND TR.ID=T1.ID;

Method #3:
SELECT
  R.ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    ID,
    ROW_NUMBER() OVER (ORDER BY HEIGHT DESC) POSITION
  FROM
    T1) R,
  T1
WHERE
  R.POSITION<=10
  AND R.ID=T1.ID;
The results of the above methods look something like this:
ID FIRSTNAME  LASTNAME   HEIGHT     AGE
---------------------------------------
 2 SUSAN      SMITH          65      20
 3 DOROTHY    SMITH          62      21
 4 JOHN       SMITH          72      35
 5 DAVID      SMITH          73      34
 7 ROBERT     SMITH          76      45
10 SUSAN      JOHNSON        65.5    20
11 DOROTHY    JOHNSON        62.5    21
12 JOHN       JOHNSON        72.5    35
13 DAVID      JOHNSON        73.5    34
15 ROBERT     JOHNSON        79      45

Method #4:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE,
    (FIRST_VALUE(HEIGHT) OVER (ORDER BY HEIGHT DESC))*HEIGHT MY_PROD,
    MAX_PROD
  FROM
    (SELECT
      MAX(PROD) MAX_PROD
    FROM
      (SELECT
        HEIGHT*LEAD(HEIGHT,9,0) OVER (ORDER BY HEIGHT DESC) PROD
      FROM
        T1)),
    T1)
WHERE
  MY_PROD>=MAX_PROD;

Method #5:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  T1
MINUS
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    RANK() OVER (ORDER BY HEIGHT)/COUNT(*) OVER (PARTITION BY 1) ROW_PERCENT,
    (COUNT(*) OVER (PARTITION BY 1)-10)/COUNT(*) OVER (PARTITION BY 1) CUT_OFF,
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1)
WHERE
  ROW_PERCENT<=CUT_OFF;

Method #6:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  (SELECT
    RANK() OVER (ORDER BY HEIGHT)/COUNT(*) OVER (PARTITION BY 1) ROW_PERCENT,
    (COUNT(*) OVER (PARTITION BY 1)-10)/COUNT(*) OVER (PARTITION BY 1) CUT_OFF,
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1)
WHERE
  ROW_PERCENT>CUT_OFF;

Method #7:
SELECT
  T1.ID,
  T1.FIRSTNAME,
  T1.LASTNAME,
  T1.HEIGHT,
  T1.AGE
FROM
  (SELECT
    MAX(COUNTER) COUNTER
  FROM
    (SELECT
      LEVEL COUNTER
    FROM
      DUAL
    CONNECT BY
      LEVEL<=10)) C,
 (SELECT
    RANK() OVER (ORDER BY HEIGHT DESC) RANKING,
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1) T1
WHERE
  T1.RANKING<=C.COUNTER;

Method #8:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
 (SELECT
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE
  FROM
    T1
  ORDER BY
    HEIGHT DESC)
WHERE
  ROWNUM<=10;

Method #9:
SELECT
  T1.ID,
  T1.FIRSTNAME,
  T1.LASTNAME,
  T1.HEIGHT,
  T1.AGE
FROM
  (SELECT
    ID,
    FIRSTNAME,
    LASTNAME,
    HEIGHT,
    AGE,
    COUNT(*) OVER (ORDER BY HEIGHT DESC) POSITION
  FROM
    T1) T1,
  (SELECT
    LEVEL COUNTER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10) C
WHERE
  T1.POSITION=C.COUNTER;

Method #10:
SELECT
  ID,
  FIRSTNAME,
  LASTNAME,
  HEIGHT,
  AGE
FROM
  T1
WHERE
  HEIGHT>
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT <
(SELECT
  MAX(HEIGHT)
FROM
  T1
WHERE
  HEIGHT<
(SELECT
   MAX(HEIGHT)
FROM
  T1
)))))))))));

Method #13 (loosely inspired by the above SQL statement):
SELECT
  T1.ID,
  T1.FIRSTNAME,
  T1.LASTNAME,
  T1.HEIGHT,
  T1.AGE
FROM
  T1,
  (SELECT
    T1.HEIGHT,
    COUNT(*) RANKING
  FROM
    T1,
    T1 T2
  WHERE
    T1.HEIGHT<=T2.HEIGHT
  GROUP BY
    T1.HEIGHT
  HAVING
    COUNT(*) BETWEEN 1 AND 10) T2
WHERE
  T1.HEIGHT=T2.HEIGHT;

Method #14 (a slight modification of Method #13):
SELECT
  T1.ID,
  T1.FIRSTNAME,
  T1.LASTNAME,
  T1.HEIGHT,
  T1.AGE
FROM
  T1,
  (SELECT
    T1.HEIGHT,
    COUNT(*) OVER (PARTITION BY 1) TOTAL_COUNT,
    COUNT(*) RANKING
  FROM
    T1,
    T1 T2
  WHERE
    T1.HEIGHT>T2.HEIGHT
  GROUP BY
    T1.HEIGHT) T2
WHERE
  (T2.TOTAL_COUNT-T2.RANKING+1) BETWEEN 1 AND 10
  AND T2.HEIGHT=T1.HEIGHT;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 Dieter Noeth suggested the following:

SELECT t1.*
FROM t1 JOIN t1 AS t2 ON t1.HEIGHT <= t2.HEIGHT
GROUP BY t1.ID, t1.FIRSTNAME,t1.LASTNAME, t1.HEIGHT, t1.AGE
HAVING COUNT(*) <= 10

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Gerard H. Pille suggested the following:

select * from T1
 where height >= (
    select MIN(height) from T1 c1
      where 10 > (select count(*) from T1 c2 where c2.height >
c1.height)
    );