get the highest id value from second table in sql server

raj raj

I have table t1 in sql server and table t2.

t1 have id,status column and t2 have id and matchid 2 column.

t1 table we have 2 record

id status

1 active

1 inactive

and t2 table also have 2 record

id matchid

2 1

32 1

we want output like

1 32

in second table need to pick max id

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

raj raj3y ago
not require to use where active it depends on id max like 32 whether active or inactive
0

SELECT t1.id AS matchid, MAX(t2.id) AS max_id
FROM t1
INNER JOIN t2 ON t1.id = t2.matchid
WHERE t1.status = 'active'
GROUP BY t1.id;

raj raj3y ago
we have one other column as well which have different value so group by we will get 2 rows
0

SELECT t1.id, MAX(t2.id) as matchid
FROM t1
INNER JOIN t2 ON t1.id = t2.matchid
GROUP BY t1.id;