Select Max number with another column

Question

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

Possible duplicate of https://stackoverflow.com/questions/8387587/selecting-a-record-with-max-value — TheWildHealer, Apr 08 '19 at 13:56
Possible duplicate of [Selecting a Record With MAX Value](https://stackoverflow.com/questions/8387587/selecting-a-record-with-max-value) — TheWildHealer, Apr 08 '19 at 13:56

jarlh · Answer 1 · 2019-04-08T13:57:46.740

5

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;

edited Apr 08 '19 at 13:57

answered Apr 08 '19 at 13:56

jarlh

42,561
8
45
63

awesome! so Max function isn't needed? Is it possible to write it using Max function and still get both columns and just the max row? – JBinson88 Apr 08 '19 at 14:00
@JBinson88 yes, you can do that as well, but if you only need to get one vendor_id, then jaralh approach is your best approach. – iSR5 Apr 08 '19 at 15:55

score 0 · Answer 2 · answered Apr 08 '19 at 14:03

0

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)

answered Apr 08 '19 at 14:03

zafrin

434
4
11

Select Max number with another column

2 Answers2