Archief - PL/SQL Minste aantal bepalen.

"ROLNR"	"NEGNR"	"DATUM"	"PRIJS"
115	4	17/07/80	575
115	5	17/07/80	690
115	12	18/07/80	230
127	5	27/06/83	460
127	10	27/06/83	575
127	15	27/06/83	690
127	20	28/06/83	230
135	15	15/01/87	""
135	16	15/01/87	""
175	7	09/02/92	345
176	4	10/02/92	230
176	5	""	402,5
176	20	28/02/92	207
176	22	28/02/92	258,75
180	13	21/03/92	287,5
180	21	21/03/92	345
180	22	21/03/92	345
225	7	12/12/94	345
225	12	12/12/94	287,5
135	17	16/01/87	172,5

CREATE OR REPLACE 
FUNCTION KLEINSTE_FOTOROL 
RETURN NUMBER 
AS 
v_rolnr NUMBER;
BEGIN

  SELECT COUNT(DISTINCT rolnr)
  INTO v_rolnr
  FROM OPNAME
  
  RETURN v_rolnr;
  
END KLEINSTE_FOTOROL;

SELECT RolNr INTO v_rolnr FROM
( SELECT RolNr, Count(1) As Aantal FROM Opname GROUP BY RolNr )
WHERE Aantal = Min(Aantal)

select rolnr, count(*)
from opname group by rolnr
order by count(*) asc

CREATE OR REPLACE 
FUNCTION KLEINSTE_FOTOROL 
RETURN NUMBER 
AS 
v_rolnr NUMBER;
v_tmp NUMBER;
BEGIN

  select rolnr into v_rolnr
  from
  (
    select rolnr, count(*)
    from opname
    GROUP BY rolnr
    order by count(*) asc
  )
  where rownum = 1;
  
  RETURN v_rolnr;
  
END KLEINSTE_FOTOROL;