CODEWITHSUNDEEP
sqlsql-server
BigIWT
BigIWT

Reputation: 293

MS-SQL max ID with inner join

Can't see the wood for the trees on this and I'm sure it's simple. I'm trying to return the max ID for a related record in a joined table

Table1

NiD Name
1 Peter
2 John
3 Arthur

Table2

ID NiD Value
1 1 5
2 2 10
3 3 10
4 1 20
5 2 15

Max Results

NiD ID Value
1 4 20
2 5 15
3 3 10

Upvotes: 2

Views: 1536

Answers (4)

Eli
Eli

Reputation: 2608

Here's what I have used in similar situations, performance was fine, provided that the data set wasn't too large (under 1M rows).

SELECT 
    table1.nid
    ,table2.id
    ,table2.value
FROM table1
INNER JOIN table2 ON table1.nid = table2.nid
WHERE table2.value = (
    SELECT MAX(value)
    FROM table2
    WHERE nid = table1.nid)
ORDER BY 1

Upvotes: 0

KtX2SkD
KtX2SkD

Reputation: 752

This is how I'd do it, I think ID and Value will be NULL when Table2 does not have a corresponding entry for a Table1 record:

SELECT NiD, ID, [Value]
FROM Table1
OUTER APPLY (
    SELECT TOP 1 ID, [Value]
    FROM Table2
    WHERE Table1.NiD = Table2.NiD
    ORDER BY [Value] DESC
) AS Top_Table2

Upvotes: 0

SE1986
SE1986

Reputation: 2740

CREATE TABLE Names
(
    NID INT,
    [Name] VARCHAR(MAX)
)

CREATE TABLE Results
(
    ID INT,
    NID INT,
    VALUE INT
)

INSERT INTO Names VALUES (1,'Peter'),(2,'John'),(3,'Arthur')
INSERT INTO Results VALUES (1,1,5),(2,2,10),(3,3,10),(4,1,20),(5,2,15)

SELECT  a.NID,
        r.ID,
        a.MaxVal
FROM    (
            SELECT  NID,
                    MAX(VALUE) as MaxVal
            FROM    Results r
            GROUP BY NID
        ) a
        JOIN Results r
            ON a.NID = r.NID AND a.MaxVal = r.VALUE
ORDER BY NID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use row_number() for this:

select NiD, ID, Value
from (select t2.*,
             row_number() over (partition by NiD order by ID desc) as seqnum
      from table2 t2
     ) t2
where seqnum = 1;

As the question is stated, you do not need table1, because table2 has all the ids.

Upvotes: 1

Related Questions