On Tue, 16 Sep 2003 21:39:32 +0200, Jørgen Müller wrote:
> En lystfisker har været så dygtig at fange to store fisk af samme art. Begge
> fisk er blandt de 10 største fanget det år, men han kan kun komme på listen
> med een fisk.
> F.eks. nr 1 og 2 er fanget af den samme, så skal nr. 1 være på listen, nr. 2
> skal ud og nr. 3 op som nr. 2.
> Giver det mening?
Det tror jeg.
Jeg har skabt lidt test-data for at have noget at rive, se til sidst. Jeg
har valgt kun at arbejde med én fiskekategori for at gøre tingene
enklere her. Hvis du gerne vil have noget, der passer mere præcist ind i
dit setup, må du selv tilvejebringe nogle test-data som der kan arbejdes
med.
For at mindske behovet for test-data, har jeg valgt at operere med top-5 i
stedet for top-10. Og jeg har skabt et view ("pers_rek"), der finder hver
fiskers personlige rekord (se sidst). Med dette view på plads kan jeg
sortere fiskerne efter højeste personlige rekord:
SELECT maxvaegt,navn
FROM pers_rek JOIN fisker ON pers_rek.fisker_id=fisker.fisker_id
ORDER BY maxvaegt DESC;
----------+--------------------+
maxvaegt | navn |
----------+--------------------+
19 | Peder Jensen |
19 | Jann Wagner |
18 | Per Lassen |
18 | Dennis Kistrup |
15 | Henning Larsen |
15 | Jesper Teglgaard |
14 | Manfred Mogensen |
12 | Eberhard Jørgensen |
----------+--------------------+
Nu kan jeg finde top-5 ved følgende korrelerede subquery:
SELECT maxvaegt,navn
FROM
pers_rek AS px
JOIN fisker ON px.fisker_id=fisker.fisker_id
WHERE (
SELECT COUNT(*)
FROM pers_rek AS py
WHERE py.maxvaegt > px.maxvaegt
) < 5
ORDER BY maxvaegt DESC;
----------+------------------+
maxvaegt | navn |
----------+------------------+
19 | Peder Jensen |
19 | Jann Wagner |
18 | Per Lassen |
18 | Dennis Kistrup |
15 | Henning Larsen |
15 | Jesper Teglgaard |
----------+------------------+
Der er med disse test-data seks personer på top-5, fordi Henning Larsen
og Jesper Teglgaards respektive rekorder er de samme. Når I opererer med
decimaltalsvægte i jeres system, vil det være ret sjældent, at en top-5
bliver til mere end top-5, men muligheden er til stede med ovenstående
forespørgselstype, hvor TOP ikke benyttes.
Test-setup:
CREATE TABLE art (
art_id VARCHAR(50) PRIMARY KEY
);
CREATE TABLE fisker (
fisker_id INT PRIMARY KEY,
navn varchar(60)
);
CREATE TABLE fangst (
fangst_id INT PRIMARY KEY,
vaegt INT,
dato SMALLDATETIME,
art_id VARCHAR(50) REFERENCES art,
fisker_id INT REFERENCES fisker
);
CREATE VIEW pers_rek AS
SELECT *
FROM (
SELECT maxvaegt, fisker_id
FROM (
SELECT max(vaegt) AS maxvaegt,fisker_id
FROM fangst
GROUP BY fisker_id
) foo
) bar
;
INSERT INTO art VALUES ('Skrubbe');
INSERT INTO art VALUES ('Laks');
INSERT INTO fisker VALUES (1, 'Peder Jensen');
INSERT INTO fisker VALUES (3, 'Dennis Kistrup');
INSERT INTO fisker VALUES (4, 'Jann Wagner');
INSERT INTO fisker VALUES (5, 'Per Lassen');
INSERT INTO fisker VALUES (6, 'Henning Larsen');
INSERT INTO fisker VALUES (7, 'Manfred Mogensen');
INSERT INTO fisker VALUES (8, 'Jesper Teglgaard');
INSERT INTO fisker VALUES (9, 'Eberhard Jørgensen');
INSERT INTO fangst VALUES (1, 18, '2003-04-14', 'Laks', 5);
INSERT INTO fangst VALUES (2, 17, '2003-04-02', 'Laks', 1);
INSERT INTO fangst VALUES (3, 15, '2003-04-02', 'Laks', 3);
INSERT INTO fangst VALUES (4, 14, '2003-04-02', 'Laks', 3);
INSERT INTO fangst VALUES (5, 13, '2003-03-01', 'Laks', 1);
INSERT INTO fangst VALUES (6, 19, '2002-03-01', 'Laks', 4);
INSERT INTO fangst VALUES (7, 8, '2002-08-01', 'Laks', 4);
INSERT INTO fangst VALUES (9, 13, '2002-12-01', 'Laks', 5);
INSERT INTO fangst VALUES (10, 15, '2002-12-01', 'Laks', 1);
INSERT INTO fangst VALUES (11, 19, '2003-07-01', 'Laks', 1);
INSERT INTO fangst VALUES (12, 14, '2003-04-01', 'Laks', 1);
INSERT INTO fangst VALUES (13, 14, '2003-06-01', 'Laks', 5);
INSERT INTO fangst VALUES (14, 16, '2003-06-01', 'Laks', 3);
INSERT INTO fangst VALUES (15, 14, '2003-06-01', 'Laks', 3);
INSERT INTO fangst VALUES (17, 15, '2003-08-01', 'Laks', 4);
INSERT INTO fangst VALUES (18, 15, '2003-07-15', 'Laks', 6);
INSERT INTO fangst VALUES (19, 15, '2003-06-15', 'Laks', 6);
INSERT INTO fangst VALUES (20, 18, '2003-05-20', 'Laks', 3);
INSERT INTO fangst VALUES (21, 15, '2003-07-11', 'Laks', 8);
INSERT INTO fangst VALUES (22, 17, '2002-07-11', 'Laks', 1);
INSERT INTO fangst VALUES (23, 12, '2002-07-10', 'Laks', 9);
INSERT INTO fangst VALUES (24, 14, '2002-07-18', 'Laks', 7);
INSERT INTO fangst VALUES (25, 13, '2002-04-18', 'Laks', 7);
--
Greetings from Troels Arvin, Copenhagen, Denmark
|