GROUP BY with MAX Date

Question

The source table is:

+----------------------------+
| Col A | Col B |    Col C   |
+-------+--------------------+
|  100  |    1  |  1/2/2012  |
|  100  |    2  |  1/2/2012  |
|  100  |    3  |  1/2/2012  |
|  100  |    1  |  5/2/2012  |
|  100  |    2  |  5/2/2012  |
|  100  |    3  |  5/2/2012  |
+-------+-------+------------+

Desired result:

+-----------------------------+
| Col A | Col B  |    Col C   |
+-------+--------+------------+
|  100  |    1   |   5/2/2012 |
|  100  |    2   |   5/2/2012 |
|  100  |    3   |   5/2/2012 |
+-------+--------+------------+

How do we obtain it using SQL?

I tried

SELECT * FROM Table A
GROUP BY Col C
HAVING Col C = max(Col C) AND Col A = '100'

But it does not seem to be working.

What [RDBMS](http://en.wikipedia.org/wiki/Relational_database_management_system) you are using? `RDBMS` stands for *Relational Database Management System*. `RDBMS is the basis for SQL`, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, etc... — John Woo, May 03 '13 at 16:28

score 1 · Answer 1 · answered May 03 '13 at 16:30

1

Isn't it like this?

SELECT ColA, ColB, MAX(ColC) ColC
FROM   TableName
GROUP  BY ColA, ColB

answered May 03 '13 at 16:30

John Woo

258,903
69
498
492

1

You can addition add a `WHERE` to filter only specific value of `ColA` example, `WHERE ColA = '100'` – John Woo May 03 '13 at 16:37

score 0 · Answer 2 · answered May 03 '13 at 16:36

0

http://sqlfiddle.com/#!2/7d6f5/6/0

You can use MAX(). Please see the fiddle above

SELECT a, b, MAX(c) FROM a GROUP BY a, b
into the consideration if this is your schema
CREATE TABLE a( a INT, b INT, c DATE );

answered May 03 '13 at 16:36

Willy Pt

1,795
12
20

My suggestion is to post your query from the sql fiddle in your answer. Don't just post a link to the sql fiddle. – Taryn May 03 '13 at 16:36

score 0 · Answer 3 · answered May 03 '13 at 16:43

0

I did this quickly in ms access,

SELECT 
 cola, colb, max(colc)
FROM MyTbl
where cola=100
group by cola,colb

enter image description here

answered May 03 '13 at 16:43

MrSimpleMind

7,890
3
40
45

GROUP BY with MAX Date

3 Answers3