How do I use max() in sql

Question

I have a table that looks like this:

col1 col2 col3 ------ ----- ----- A 1 trout A 2 trout B 1 bass C 1 carp C 2 tuna D 1 salmon

I really only want to select the rows with the max value for col2.

The query I want to generate would return the following:

col1 col2 col3 ------ ----- ----- A 2 trout B 1 bass C 2 tuna D 1 salmon

I've tried something like this:

select col1, max (col2) as mCol2, col3 from mytable group by col1, col2

In this case I get:

col1 Mcol2 col3 ------ ----- ----- A 2 trout B 1 bass C 1 carp C 2 tuna D 1 salmon

As you can see, I still get C, 1, carp, when I'm only wanting C, 2, tuna.

I've considered trying to do something like

select col1, col2, col3 from mytable where col1-n-col2 in ( select col1, max (col2) as mCol2 from mytable) group by col1, col2

But I don't think that's legal in SQL. What obvious solution have I missed?

Mark Byers · Accepted Answer · 2011-09-30 21:09:36Z

If (col1, col2) is unique, or if you don't mind duplicates in the case of ties:

SELECT T1.col1, T1.col2, T1.col3 FROM mytable T1 JOIN ( SELECT col1, MAX (col2) AS mCol2 FROM mytable GROUP BY col1 ) T2 ON T1.col1 = T2.col1 AND T1.col2 = T2.mCol2

If you want to choose any row in the case of a tie (requires ROW_NUMBER support, i.e. not MySQL):

SELECT col1, col2, col3 FROM ( SELECT col1, col2, col3, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 DESC) AS rn FROM mytable ) T1 WHERE rn = 1

Mike Meyers · Accepted Answer · 2011-10-02 08:18:07Z

Analytic functions are by far the best way to solve this sort of problem as they will perform much better than a sub query over a large data set.

Either try the second query provided by Mark Byers or try the query shown below:

select col1, max (col2) as mCol2, max (col3) keep (dense_rank last order by col2) as col3 from mytable group by col1

This uses the LAST analytic function to get the last value in each group after ordering by col2. The MAX used for col3 is required by the syntax but is only really a tie-breaker.

+1 for being the best answer here. Although you are not using analytic functions here at all, only aggregate functions ...
@RobvanWijk I realised I was using an aggregate function just before submitting the answer and it seemed more trouble than it was worth to change (although I'm now writing a long comment to justify it...). For me analytics are the best way to solve most problems and I use the first and last functions fairly regularly in both the analytic and aggregate forms.

BIBD · Accepted Answer · 2011-09-30 21:26:28Z

you were close

select t.col1, t.col2, t.col3 from mytable t, (select col1, max (col2) as mx from mytable group by col1) m where m.col1 = t.col1 and m.mx = t.col2

marc_s · Accepted Answer · 2011-09-30 21:15:14Z

You didn't specify which database you're using - on SQL Server and a few others, you could use a CTE (Common Table Expression) with a windowing function to get your rows:

;WITH HighestOnly AS ( SELECT col1, col2, col3, ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2 DESC) AS 'RowNum' FROM MyTable ) SELECT col1, col2, col3 FROM HighestOnly WHERE RowNum = 1

This will "partition" your data by the criteria specified (col1) and dish out consecutive numbers for each data partition, starting at one - ordered by a second criteria given (col2 DESC). So for each "partition" of data, the row with the highest value in col2 will have RowNum = 1 - and that's what I'm selecting here.

dave · Accepted Answer · 2011-09-30 21:19:09Z

Here is one approach

With max_qry as (select col1, max(col2) as mcol2 from mytable group by col1) select m.col1, m.col2, m.col3 from mytable m join max_qry on m.col1 = mq.col1 and m.col2 = mq.mcol2

paparazzo · Accepted Answer · 2011-09-30 21:22:13Z

Try

 select mytable.col1, mytable.col2, mytable.col3 from mytable join ( select col1, max(col2) as col2 from mytable group by col1) as mytablemax on mytable.col1 = mytablemax.col1 and mytable.col2 = mytablemax.col2

Alex · Accepted Answer · 2011-09-30 22:10:18Z

hei..

I don't have an Oracle database installed but for MySQL this is wrong..

It is because of your space in syntax... the correct version is

SELECT MAX(column) ...

and not

SELECT MAX (column) ...

I don't think that with space is recognized as a function...

Collectives™ on Stack Overflow

How do I use max() in sql

7 Answers 7

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Related