How do I use max() in sql

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 mytabl开发者_运维技巧e
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?

---

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

---

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.

---

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

---

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.

---

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

---

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

---

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...