Geek asks and answers

Sql to take the maximum number from both tables?

I need to request the maximum identifier from two tables, and I need to take the identifier of which is larger. I am using sqlserver.

Inquiries

SELECT MAX(a.ID)
FROM   tableA a

SELECT MAX(b.ID)
FROM   tableB b

If the tableAmaximum ID is 20 and the tableBmaximum ID is 30, then UNIONonly 30 should return both table queries.

Is it possible to combine both requests into one request in order to return the maximum ID?

+5
source share
3 answers

This is based on what you said, UNIONboth tables and get the maximum value.

SELECT max(ID)
FROM
(
    select max(ID) ID from tableA
    UNION
    select max(ID) ID from tableB
) s

or

SELECT max(ID)
FROM
(
    select ID from tableA
    UNION
    select ID from tableB
) s
+6
source 
SELECT MAX(id)
FROM (SELECT ID FROM tableA
      UNION
      SELECT ID FROM tableB) AS D
+3
source 

SELECT MAX(CASE WHEN a.ID > b.ID THEN a.ID ELSE b.ID END)
FROM tableA a CROSS JOIN tableB b

SQLFiddle

0

More articles: