• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 974
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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