[MSSQL] Records teruggeven met hoogste aantal

[Phenomenon]

Ik heb de volgende query

SQL:

SELECT     
    dbo.Contracten.ContractID, 
    dbo.Contracten.Naam, 
    dbo.TrajectGegevens.BowDoelperspectief, 
    COUNT(*) AS Aantal
FROM 
    dbo.Clientgegevens 
INNER JOIN
    dbo.TrajectGegevens 
        ON dbo.TrajectGegevens.ClientID = dbo.Clientgegevens.ID 
INNER JOIN
        dbo.Contracten 
        ON dbo.TrajectGegevens.ContractID = dbo.Contracten.ContractID
GROUP BY 
    dbo.Contracten.ContractID, 
    dbo.Contracten.Naam, 
    dbo.TrajectGegevens.BowDoelperspectief

Welke als uitkomst bijvoorbeeld onderstaand geeft:

code:

1   Naam1   Doelperspectief1    38
1   Naam1   Doelperspectief2    12
1   Naam1   Doelperspectief3    6
2   Naam2   Doeperspectief1     112
2   Naam2   Doelperspectief2     60

Wat ik nu wil is van deze resultataten alleen het record met de hoogste waarden terugzien. Dus het volgende resultaat wil ik krijgen

code:

1  Naam1  Doelperspectief1  38
2  Naam2  Doelperspectief1  112

Dit krijg ik alleen niet voor elkaar. Ik zit het te zoeken in MAX(), maar kom er gewoon niet uit. Iemand die me de goede richting in kan helpen?

In onderstaande richting zit ik te denken

SQL:

SELECT 
    BowDoelperspectief, 
    ContractId, 
    MAX(Aantal)
FROM
    (
        SELECT     
            dbo.Contracten.ContractID, 
            dbo.Contracten.Naam, 
            dbo.TrajectGegevens.BowDoelperspectief, 
            COUNT(*) AS Aantal
        FROM 
            dbo.Clientgegevens 
        INNER JOIN
            dbo.TrajectGegevens 
                ON dbo.TrajectGegevens.ClientID = dbo.Clientgegevens.ID 
        INNER JOIN
                dbo.Contracten 
                ON dbo.TrajectGegevens.ContractID = dbo.Contracten.ContractID
        GROUP BY 
            dbo.Contracten.ContractID, 
            dbo.Contracten.Naam, 
            dbo.TrajectGegevens.BowDoelperspectief
    ) as tmpTabel
GROUP BY 
    ContractID, 
    BowDoelperspectief
ORDER BY ContractID

[pedorus]

[search=groupwise max]
[google=groupwise max]

[skabouter]

Je kunt iets doen met de IN operator, zoals hieronder (niet getest btw)

SQL:

SELECT     
    dbo.Contracten.ContractID, 
    dbo.Contracten.Naam, 
    dbo.TrajectGegevens.BowDoelperspectief, 
    COUNT(*) AS Aantal
FROM 
    dbo.Clientgegevens 
INNER JOIN
    dbo.TrajectGegevens 
        ON dbo.TrajectGegevens.ClientID = dbo.Clientgegevens.ID 
INNER JOIN
        dbo.Contracten 
        ON dbo.TrajectGegevens.ContractID = dbo.Contracten.ContractID
WHERE
        dbo.Contracten.ContractID IN (
SELECT dbo.Contracten.ContractID, MAX(dbo.TrajectGegevens.BowDoelperspectief) FROM dbo.Contracten
INNER JOIN
    dbo.TrajectGegevens 
        ON dbo.TrajectGegevens.ClientID = dbo.Clientgegevens.ID 
INNER JOIN
        dbo.Contracten 
        ON dbo.TrajectGegevens.ContractID = dbo.Contracten.ContractID
GROUP BY 
    dbo.Contracten.ContractID, 
    dbo.Contracten.Naam, 
    dbo.TrajectGegevens.BowDoelperspectief
)
GROUP BY 
    dbo.Contracten.ContractID, 
    dbo.Contracten.Naam, 
    dbo.TrajectGegevens.BowDoelperspectief

[Phenomenon]

pedorus schreef op maandag 10 november 2008 @ 14:03:
[search=groupwise max]
[google=groupwise max]

Zo te lezen wordt dat alleen ondersteund in MySQL en ik gebruik MSSQL. Zit nu te zoeken naar een equivalent, maar die zie ik zo snel nog niet.

@skabouter, die query van jou werkt niet, omdat ik aan ContractID alleen niet genoeg heb. Deze zou samen moeten zijn met BowDoelPerspectief en Aantal.

[pistole]

Phenomenon schreef op maandag 10 november 2008 @ 14:21:
[...]
Zo te lezen wordt dat alleen ondersteund in MySQL en ik gebruik MSSQL. Zit nu te zoeken naar een equivalent, maar die zie ik zo snel nog niet.

Als je goed leest dan staan er ansi-sql oplossingen, en die werken in (bijna) alle DBMS'en.

[winkbrace]

Het is iets lastiger omdat je eerst die count(*) gebruikt, maar dit zou ook moeten werken:

code:

select ContractID
,      Naam
,      BowDoelperspectief
,      Aantal
from
(
    SELECT     
        dbo.Contracten.ContractID, 
        dbo.Contracten.Naam, 
        dbo.TrajectGegevens.BowDoelperspectief, 
        COUNT(*) AS Aantal
        MAX(COUNT(*)) over (partition by dbo.Contracten.ContractID) AS Max_Aantal
    FROM 
        dbo.Clientgegevens 
    INNER JOIN
        dbo.TrajectGegevens 
            ON dbo.TrajectGegevens.ClientID = dbo.Clientgegevens.ID 
    INNER JOIN
            dbo.Contracten 
            ON dbo.TrajectGegevens.ContractID = dbo.Contracten.ContractID
    GROUP BY 
        dbo.Contracten.ContractID, 
        dbo.Contracten.Naam, 
        dbo.TrajectGegevens.BowDoelperspectief
)
where  Aantal = Max_Aantal

[ Voor 7% gewijzigd door winkbrace op 10-11-2008 17:58 ]