Solved

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

Posted on 2012-03-29
3
382 Views
Last Modified: 2012-03-30
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
Comment
Question by:lulubell-b
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 2

Accepted Solution

by:
dantheanswerman earned 250 total points
ID: 37784365
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
 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 250 total points
ID: 37784705
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
 

Author Comment

by:lulubell-b
ID: 37787308
This is perfect. I was overthinking it.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question