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
lulubell-bAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.