Solved

Selecting BOTTOM values

Posted on 2008-10-15
6
240 Views
Last Modified: 2012-06-27
I have the following data in a table

COL1          COL2          COL3
--------         --------        --------
1                 2000           2
1                 2001           1
1                 2002           4
1                 2003           3
2                 2000           1
2                 2001           2
2                 2002           4
2                 2003           3

I need to select the records that had the maximum values of COL3 for each COL1
In other words I need to select the records with the values 1,2002,4 AND 2,2002,4

How would I go about this?
0
Comment
Question by:acsakany
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 13

Expert Comment

by:devsolns
ID: 22722495
select * from tbl where [COL3] = (select max([COL3]) from tbl)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22722529
select a.* from YourTable a
join (select col1, max(col3) mcol3 from YourTable) b
on a.col1=b.col1
and a.col2=b.col2


or


select * from yourtable a
where col3 = (select max(col3) from yourtable where col1 = a.col1)
0
 

Author Comment

by:acsakany
ID: 22722651
Wouldn't both of these solutions only return one record? I need the MAX for COL3 each time COL1 changes.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 50 total points
ID: 22722666
No.  Each one will return the max for each col1.

So for your example above:

COL1          COL2          COL3
--------         --------        --------
1                 2002           4
2                 2002           4

0
 
LVL 13

Expert Comment

by:devsolns
ID: 22722740
Nope each will return the max of the column
0
 

Author Closing Comment

by:acsakany
ID: 31506352
Yes you are right. Thank you very much.
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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

749 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