[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Retrieving column that has a MAX(value) in another column.

Hello,

I'm trying to build this query to give me the element that the sequence number is the greatest value.

For example,

I have two records:

DST_NM          SEQ_NUM
New York              1
Cleveland              2

I need to select the DST_NM with the MAX(SEQ_NUM).

Results:

Select DST_NM , MAX(SEQ_NUM) FROM Table
GROUp by  DST_NM

=  Cleveland              2

I don't know what I'm doing wrong :)

Thanks
0
lulubell-b
Asked:
lulubell-b
2 Solutions
 
dantheanswermanCommented:
Try this test code -- select the top one and order desc by seq_num!! no grouping needed.

create table #Test (DST_NM nvarchar(20), seq_num int)
insert into #Test values ('New York', 1)
insert into #Test values ('Cleveland', 2)



select top 1 dst_nm, seq_num from #Test
order by seq_num desc
0
 
QlemoDeveloperCommented:
If you do a group by, all aggregate functions (min, max, ...) are applied within that group. Grouping by DST_NM will result in one row per DST_NM, showing the max SEQ_NUM for all same DST_NM. Obviously not what you are after.

Using TOP 1 is indeed the easiest way, if you only want one single row. If it gets more complex, that does not work (like getting the record with max SEQ_NUM for each DST_NM value).
0
 
lulubell-bAuthor Commented:
This is perfect. I was overthinking it.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now