MAX function

[catchingup]

I am trying to use the MAX function to get the most recent value for a column and not having success using the following formula:

SELECT DISTINCT COL3, COL2, COL20
FROM VALUETABLE AS VF
WHERE COL20 =
(SELECT MAX(COL20)
FROM VALUETABLE
WHERE COL2 = VF.COL2)
ORDER BY COL3, COL2

The values I am returning by this statement are inaccurate.

If you were to look at the table - VALUETABLE - as is, it looks like the following:

COL3 COL2 COL20
JC FGH 2007-06-30
JC FGH 2006-05-31
JC FGH 2004-08-31
JC MCW 2007-02-30
JC MCW 2007-06-30
TG BT 2006-11-30
TG BT 2007-06-30
TG FGH 2007-03-31
TG MCW 2004-08-31
TG MCW 2007-11-30
TG MCW 2005-02-28
FH ED 2003-04-01


What I am looking to get is a value for COL3 (which may be duplicated depending upon the number of associated entries in COL2); one value for COL2 and the max value of COL20 that goes along with COL2.

So my result set for the example above would be:
COL3 COL2 COL20
JC FGH 2007-06-30
JC MCW 2007-06-30
TG BT 2007-06-30
TG FGH 2007-03-31
TG MCW 2007-11-30
FH ED 2003-04-01

Any help is greatly appreciated!

Thank you

 

[ptheriault]

Not tested but this might work.

SELECT VF.COL3, a.COL2, VF.COL20
FROM VALUETABLE VF
JOIN
(SELECT COL2,
MAX(COL20)
FROM VALUETABLE) a
ON a.COL2 = VF.COL2

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Oops. Forgot group by.


SELECT VF.COL3, a.COL2, VF.COL20
FROM VALUETABLE VF
JOIN
(SELECT COL2,
MAX(COL20)
FROM VALUETABLE
GROUP BY COL2) a
ON a.COL2 = VF.COL2

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Crowley16]

erm, a simple group by will do this...

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

--------------------
Procrastinate Now!

 

[ptheriault]

erm,,
Go with Crowley's example. It's correct where as mine returns to many rows.

DECLARE @VALUETABLE TABLE (COL3 CHAR(2), COL2 VARCHAR(4), COL20 VARCHAR(20))

INSERT INTO @VALUETABLE VALUES(   'JC'     ,  'FGH'   ,     '2007-06-30')
INSERT INTO @VALUETABLE VALUES(   'JC'   ,    'FGH' ,        '2006-05-31')
INSERT INTO @VALUETABLE VALUES(    'JC'    ,   'FGH' ,       '2004-08-31')
INSERT INTO @VALUETABLE VALUES(    'JC'    ,   'MCW' ,       '2007-02-30')
INSERT INTO @VALUETABLE VALUES(    'JC'    ,   'MCW' ,       '2007-06-30')
INSERT INTO @VALUETABLE VALUES(    'TG'    ,   'BT'  ,       '2006-11-30')
INSERT INTO @VALUETABLE VALUES(    'TG'    ,   'BT'  ,       '2007-06-30')
INSERT INTO @VALUETABLE VALUES(    'TG'    ,   'FGH' ,       '2007-03-31')
INSERT INTO @VALUETABLE VALUES(    'TG'    ,   'MCW' ,       '2004-08-31')
INSERT INTO @VALUETABLE VALUES(    'TG'    ,   'MCW' ,       '2007-11-30')
INSERT INTO @VALUETABLE VALUES(   'TG'    ,  'MCW'  ,        '2005-02-28')
INSERT INTO @VALUETABLE VALUES(    'FH'    ,   'ED'  ,       '2003-04-01')


select col3, col2, max(col3)
from @VALUETABLE
group by col3, col2

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[catchingup]

Thank you!

This ---

select col3, col2, max(col3)
from @VALUETABLE
group by col3, col2

worked exactly as I needed it to