CODEWITHSUNDEEP
sqloracle-databasedistinct
TinsThom
TinsThom

Reputation: 33

Distinct Records based on two columns

I have a table testing with two fields name and com_date. The values in the table are as shown below. date format is mm/dd/yyyy

Name | Com_Date

MC  |  2/1/2013
MC  |  2/2/2013
MC  |  2/5/2013
MR  |  2/4/2013
MR  |  2/5/2013
MU  |  2/1/2013
MU  |  2/2/2013

I Need to query for records based on distinct Name and the latest date. latest record first and distinct record based on name. like: 

MC  |  2/5/2013
MR  |  2/5/2013
MU  |  2/2/2013

Upvotes: 0

Views: 1033

Answers (3)

Taryn
Taryn

Reputation: 247710

Al you will need to use an aggregate function Max() with a GROUP BY:

select name, max(com_date) MaxCom_Date
from yourtable
group by name

See SQL Fiddle with Demo.

This can also be written using row_number() over(partition by...):

select name, com_date
from
(
  select name, 
    com_date,
    row_number() over(partition by name order by com_date desc) rn
  from yourtable
) 
where rn =1

See SQL Fiddle with Demo

The result for both is:

| NAME | MaxCom_Date |
---------------------
|   MC | 02/05/2013  |
|   MR | 02/05/2013  |
|   MU | 02/02/2013  |

Upvotes: 2

pasc
pasc

Reputation: 11

If your com_date column is in date format you can just do the following:

SELECT name, TO_CHAR(MAX(com_date), MM/DD/YYYY) FROM testing GROUP BY name ORDER BY name ASC;

If your date isn't in date format, you'll need to convert it before you stick it in MAX()

Upvotes: 0

spin_eight
spin_eight

Reputation: 4025

SELECT name, MAX(date)
 FROM table
 GROUP BY name
 ORDER BY 2;

Upvotes: 1

Related Questions