Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 973
  • Last Modified:

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
0
red27man
Asked:
red27man
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
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
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 
pek99Commented:
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
 
Jay ToopsCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now