• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2066
  • Last Modified:

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

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
campbell1972
Asked:
campbell1972
1 Solution
 
derekkrommCommented:
select * from query1 a join (select max(column1) maxc1, column2 from query1) b on a.column1 = b.maxc1 and a.column2 = b.column2
0
 
BinuthCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now