CODEWITHSUNDEEP
sqlsql-serversql-server-2014
JBinson88
JBinson88

Reputation: 123

Select Max number with another column

I am writing a simple query to get the VENDOR_ID that has the MAX ADDRESS_SEQ_NUM so that I would have the following output:

VENDOR_ID     ADDRESS_SEQ_NUM
76109A        81

The problem is when I write the following SQL I am getting back every VENDOR_ID and it's own max ADDRESS_SEQ_NUM, whereas I want just the VENDOR_ID and max ADDRESS_SEQ_NUM in the entire table.

SELECT VENDOR_ID, MAX(ADDRESS_SEQ_NUM)
FROM PS_VNDR_ADDR_SCROL
GROUP BY VENDOR_ID

Upvotes: 0

Views: 68

Answers (2)

zafrin
zafrin

Reputation: 454

You have to do a subselect

SELECT VENDOR_ID, ADDRESS_SEQ_NUM FROM PS_VNDR_ADDR_SCROL WHERE ADDRESS_SEQ_NUM = (SELECT MAX(ADDRESS_SEQ_NUM) FROM PS_VNDR_ADDR_SCROL)

Upvotes: 0

jarlh
jarlh

Reputation: 44766

ORDER BY ADDRESS_SEQ_NUM descending. Use TOP 1 to get the first row only, i.e. the row having the highest ADDRESS_SEQ_NUM value.

SELECT TOP 1 VENDOR_ID, ADDRESS_SEQ_NUM
FROM PS_VNDR_ADDR_SCROL
ORDER BY ADDRESS_SEQ_NUM DESC;

Upvotes: 5

Related Questions