Solved

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

Posted on 2012-03-29
3
378 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
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 69

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using MERGE to UPDATE a third table 14 37
Access 2016 - query 23 60
Help Required 2 32
performance query 4 22
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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