Solved

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

Posted on 2008-09-29
2
2,056 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Add different cell to otherwise similiar row 4 45
Is there any way to convert exponential value to number in sql server 5 55
string fuctions 4 28
2 IIF's in Access query 25 44
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. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

839 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