select unique values (selecting the most recent record based on date)

hi

i need to select unique values based on date(the recent date)

the table is like this

name     created
aa           5/5/03
bb           6/3/04
cc           3/3/04
aa           8/8/04
bb           3/3/03

the output i am looking for is
name      created
aa           8/8/04
bb           6/3/04
cc           3/3/04

thanks
red
red27manAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pek99Connect With a Mentor Commented:
If you have duplicates with GROUP BY NAME, then you have probably "hidden" spaces in the column NAME.

Then this may work for you:

SELECT TRIM( Name), MAX(Created) FROM YourTable GROUP BY TRIM(Name);
0
 
DexstarCommented:
red27man:

> i need to select unique values based on date(the recent date)

Try this:
    SELECT Name, MAX(Created) FROM YourTable GROUP BY Name

Hope That Helps,
Dex*
0
 
Jay ToopsCommented:
This is works if you have MORE than just NAME in your source table

select *
from MyTable m
inner join ( select name, max([created]) as maxdate
                 from MyTable
                 group by name ) as x
                 on m.name= x.name
                 and m.[created] = x.maxdate )

Jay
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Jay ToopsCommented:
oops ... PLEASE delete the LAST )
the function should read

select *
from MyTable m
inner join ( select name, max([created]) as maxdate
                 from MyTable
                 group by name ) as x
                 on m.name= x.name
                 and m.[created] = x.maxdate

Jay
0
 
red27manAuthor Commented:
It still gives the duplicate values



the output i am looking for is
name      created
aa           8/8/04
bb           6/3/04
cc           3/3/04


one record each of the "name" field ( that  one record being the most recent)

let me put it the other way
i need to have  distinct value of  " name" field ( where the distinct value being selected is based on created)

thanks
shri


thanks
red
0
 
DexstarCommented:
red27man:

> let me put it the other way
> i need to have  distinct value of  " name" field ( where the distinct value being
> selected is based on created)

Did you get duplicates with my suggestion too?  The "Group By" part of it means to put all the Names in a group, so you shouldn't get any duplicates.

-D*
0
 
Jay ToopsConnect With a Mentor Commented:
add a sequential id field so that you can MAX that too.

jay then you WILL get only one record...
with mine if you have more than one identical date you can get duplicates.

Jay
0
 
red27manAuthor Commented:
Thanks...........i had to work a bit but both of them were helpful

red
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.