Group/Distinct on one col and Max on another

[peanutz]

I need a query that will find for example in the given table

Distinct Col1, with highest Col3

A  X  1
A  Y  2
A  X  3
B  Y  2
B  Z  4

So the result should be:

A  X  3
B  Z  4

[tesuji]

Hello

You could try this:

select * from t3 where c1 || to_char(c3) in (select c1 || to_char(max(c3)) from t3 group by c1) order by c1, c2, c3;

c1, c2, c3 are your three columns. The subselect generates a temporary key from grouped c1 column and the corresponding to_char(maximums of c3). The outer select also generates a temporary key from c1 und c3 without grouping and aggregating. Then all rows with identical keys are seleted. Certainly, there exist more efficient solutions, however above select also finds duplicate rows. If you don't care duplicates, try: select distinct * ...

Hope it will work, nevertheless, this query not tested.

-- tesu

[NeverLift]

Need a bit of clarification:

If there are multiple rows with the same col1 and equal to the max on col3, do you want both to appear if they are not complete duplicates (col2 differs)? If just one, how should it be chosen?

If you want all: If those rows contain multiples with the same col2, do you want just one exemplar with that col2 in the result, or all?

[yash_792]

Hi,

Try this query, it should give you the desired result.

select col1,col2,max(col3)
from table_1
group by col1,col2

I need a query that will find for example in the given table

Distinct Col1, with highest Col3

A  X  1
A  Y  2
A  X  3
B  Y  2
B  Z  4

So the result should be:

A  X  3
B  Z  4

[NeverLift]

yash_792: That won't do it. Note that only one of the A rows was to be returned,
A X 3.
Your query will return as well
A Y 2

This may do the job, depending upon the answer to my request for clarification:

Select distinct col1, col2, col3 from 
   table_1, 
   (select col1 col1ofmax, max(col3) col3max from table_1 group by col1)
where col1=col1ofmax and col3=col3max;

If two rows for a given col1 value both have the maximum col3 for that col1 but different col2 values, both will be returned.

If you want just one exemplar of the col1 and its maximum col3, choosing any of those that qualify and showing just one, but any one, of the possible col2-valued rows, it gets a bit more involved. I had posted a solution, realized it wouldn't do it, will edit this post to show one that will work . . . later, if you post saying that's what you want.

[NeverLift]

Actaully, the "one exemplar" query isn't as tough as I thought, got into using rownum which can be tricky.

Here it is:

select col1, min(col2), col3 from 
  table_1,
  (select col1 col1ofmax, max(col3) col3max from table_1 group by col1)
where col1=col1ofmax and col3=col3max
group by col1, col3;

The "disadvantage" here: If two rows both with the same maximum of col3 are present but with different col2, you'll not know that, only the lowest/first col2 value will show in the result, with no indication that there were others.

[1stDAN]

I have tested Neverlift and tesuij answers. They almost meet the example of peanutz but peanutz didn't check it so far and didnt answered question. Possibly he is not further interested in answer.