Solved

Select Max Value in Column 1 based upon Distinct Value in Column 2

Posted on 2008-09-29
2
2,055 Views
Last Modified: 2012-05-05
Experts

I am looking to finalise a SQL query which should present a subset of data from an initial query which is based upon an outer join on 2 tables.

The second query should return the maximum value in column 1 associated with each distinct value in column 2.

For example, if the first outer join returns:

Column 1   Column 2        Column 3    Column 4

123            1111               x                 y
234            1111               a                 b
456            2222               c                 d
457            2222               c                 d
267            2223               a                 b

the second query should return:

Column 1   Column 2        Column 3    Column 4

234            1111               a                 b
457            2222               c                 d
267            2223               a                 b

I would like to perform this within a single SQL statement and avoid the use of temporary tables and nested cursors if at all possible.

Any pointers would be greatly appreciated!


 
0
Comment
Question by:campbell1972
2 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
ID: 22599974
select * from query1 a join (select max(column1) maxc1, column2 from query1) b on a.column1 = b.maxc1 and a.column2 = b.column2
0
 
LVL 14

Expert Comment

by:Binuth
ID: 22603266
use GROUP BY too....
SELECT Query1.* 
FROM Table1 AS Query1
INNER JOIN (SELECT MAX(Column1) AS Col1,Column2 FROM Table1 GROUP BY Column2) Query2 
	ON Query1.Column1 = Query2.Col1	AND Query1.Column2 = Query2.Column2

Open in new window

0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

803 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