CODEWITHSUNDEEP
sqlgroup-by
user85116
user85116

Reputation: 4492

sql - getting the id from a row based on a group by

Table A

Table B

Table A links to Table b from the tableBID found in both tables.

If I want to find the row in Table A, which has the highest grade, for each row in Table B, I would write my query like this:

select max(grade) from TableA group by tableBID

However, I don't just want the grade, I want the grade plus id of that row.

Upvotes: 4

Views: 7410

Answers (3)

Dane
Dane

Reputation: 9827

Just use a little self-referencing NOT EXISTS clause:

DECLARE @tableA TABLE (tableAID int IDENTITY(1,1), tableBID int, grade int)

INSERT INTO @tableA(tableBID, grade) VALUES(10, 1)
INSERT INTO @tableA(tableBID, grade) VALUES(10, 3)
INSERT INTO @tableA(tableBID, grade) VALUES(20, 4)
INSERT INTO @tableA(tableBID, grade) VALUES(20, 8)
INSERT INTO @tableA(tableBID, grade) VALUES(30, 10)
INSERT INTO @tableA(tableBID, grade) VALUES(30, 6)

SELECT tableAID, grade
FROM @tableA ta
WHERE NOT EXISTS (SELECT 1 FROM @tableA WHERE tableBID = ta.tableBID AND grade > ta.grade)

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166346

You could try something like

SELECT  a.*
FROM    TableA a INNER JOIN
        (
            SELECT  tableBID,
                    MAX(grade) MaxGrade
            FROM    TableA
            GROUP BY tableBID
        ) B ON a.tableBID = B.tableBID AND a.grade = B.MaxGrade

Using the Sql Server 2005 ROW_NUMBER function you could also try

SELECT  tableAID,
        tableBID,
        grade
FROM    (
            SELECT  *,
                    ROW_NUMBER() OVER (PARTITION BY tableBID ORDER BY grade DESC) RowNum
            FROM    @TableA
        ) A
WHERE   a.RowNum = 1

Upvotes: 4

Russell Steen
Russell Steen

Reputation: 6612

Assuming you will only have one Max() (HUGE ASSUMPTION) you can do min(tableaid) where grade = (select max(grade) etc....)

or if you want all matching ids

SELECT TableAID, Grade
FROM TableA INNER JOIN (SELECT  tableBID,
     MAX(grade) Grade
     FROM    TableA
     GROUP BY tableBID) MaxGrade 
ON TableA.Grade = MaxGrade.Grade

Upvotes: 0

Related Questions