SQL ORACLE Select the top-N rows using join-CodePudding

Find the cats occupying the first n places in terms of the total number of mice consumed (cats with the same consumption occupy the same place!) using the join of the Kocury relation with the Kocury relation.

[this solution doesn't quite work properly] When n=1 there are two cats (Tygrys and Lysy), but should be only Tygrys.

I have 3 correct solutions but for my 4 one I have ot use JOIN operator or LEFT JOIN or sth related to JOIN:

solution 1.
SELECT pseudo, przydzial_myszy   NVL(myszy_extra, 0) "ZJADA"
FROM Kocury K
WHERE (SELECT COUNT(DISTINCT przydzial_myszy   NVL(myszy_extra, 0)) FROM Kocury`enter code here`
      WHERE przydzial_myszy   NVL(myszy_extra, 0) > K.przydzial_myszy   NVL(K.myszy_extra, 0)) < 6
ORDER BY 2 DESC;

solution 2
SELECT pseudo, przydzial_myszy   NVL(myszy_extra, 0) "ZJADA"
FROM Kocury
WHERE przydzial_myszy   NVL(myszy_extra, 0) IN (
  SELECT *
  FROM (
    SELECT DISTINCT przydzial_myszy   NVL(myszy_extra, 0)
    FROM Kocury
    ORDER BY 1 DESC
  ) WHERE ROWNUM <= 6
);

solution 3
SELECT pseudo, ZJADA
FROM
(
  SELECT pseudo,
    NVL(przydzial_myszy, 0)   NVL(myszy_extra, 0) "ZJADA",
    DENSE_RANK() OVER (
      ORDER BY przydzial_myszy   NVL(myszy_extra, 0) DESC
    ) RANK
  FROM Kocury
)
WHERE RANK <= 6;

[Kocury relation]

CodePudding user response：

I don't quite understand column names so I'll suggest a query which is somewhat simpler to understand. Read comments within code. Basically, you'd

find how many mice each cat got
rank cats by number of mice (using dense_rank analytic function; check rank and row_number as well)
finally, return cats who rank the highest

with 
temp as
  (select cat_name,
     sum(mice_consumed) sum_mice
   from cats_mice
   group by cat_name
  ),
rank_cats as
  (select cat_name,
     sum_mice,
     dense_rank() over (order by sum_mice desc) rnk
   from temp
  )
-- finally:
select cat_name,
  sum_mice
from rank_cats
where rnk < 7;

CodePudding user response：

You can use:

SELECT k.pseudo,
       MAX(k.przydzial_myszy) AS przydzial_myszy,
       MAX(k.myszy_extra) AS myszy_extra,
       MAX(k.przydzial_myszy   COALESCE(k.myszy_extra, 0)) AS total
FROM   Kocury k
       LEFT OUTER JOIN Kocury h
       ON (h.przydzial_myszy   COALESCE(h.myszy_extra, 0)
            > k.przydzial_myszy   COALESCE(k.myszy_extra, 0))
GROUP BY k.pseudo
HAVING COUNT(DISTINCT h.przydzial_myszy   COALESCE(h.myszy_extra, 0)) < 3
ORDER BY total DESC

Which for the sample data:

CREATE TABLE Kocury (pseudo, przydzial_myszy, myszy_extra) AS
SELECT 'PLACEK',   67, NULL FROM DUAL UNION ALL
SELECT 'RURA',     56, NULL FROM DUAL UNION ALL
SELECT 'LOLA',     25, 47   FROM DUAL UNION ALL
SELECT 'ZERO',     43, NULL FROM DUAL UNION ALL
SELECT 'PUSZYSTA', 20, 35   FROM DUAL UNION ALL
SELECT 'UCHO',     40, NULL FROM DUAL UNION ALL
SELECT 'MALY',     40, NULL FROM DUAL UNION ALL
SELECT 'TYGRYS',  103, 33   FROM DUAL UNION ALL
SELECT 'BOLEK',    50, NULL FROM DUAL UNION ALL
SELECT 'ZOMBI',    75, 13   FROM DUAL UNION ALL
SELECT 'LYSY',     72, 21   FROM DUAL UNION ALL
SELECT 'SZYBKA',   65, NULL FROM DUAL UNION ALL
SELECT 'MALA',     22, 42   FROM DUAL UNION ALL
SELECT 'RAFA',     65, NULL FROM DUAL;

Outputs:

PSEUDO PRZYDZIAL_MYSZY MYSZY_EXTRA TOTAL

TYGRYS 103 33 136

LYSY 72 21 93

ZOMBI 75 13 88

PSEUDO	PRZYDZIAL_MYSZY	MYSZY_EXTRA	TOTAL
TYGRYS	103	33	136
LYSY	72	21	93
ZOMBI	75	13	88

db<>fiddle here