• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • 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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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