SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Selecting data based off last date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

unigee
Starting Member

4 Posts

Posted - 05/17/2013 :  09:07:45  Show Profile  Reply with Quote
Hi all,

I'm not very profecient at SQL although I do have strong programming background, however I am finding it quite difficult trying to create a SQL query to return the results that I need.

I have a database that has several columns. I am trying to select all the rows from the last date entered for each partnumber.

Sample table

Id, Partnumber, date_entered, quantity, yadda, yadda2
10, part1,      130310,       100,      xxx,   yyy
11, part2,      130310,       50,       xxx,   yyy
12, part1,      130517,       20,       xxx,   yyy
13, part1,      130516,       500,      xxx,   yyy
14, part1,      130517,       30,       xxx,   yyy

What I would like returned is

Id, Partnumber, date_entered, quantity, yadda, yadda2
12, part1,      130517,       20,       xxx,   yyy
14, part1,      130517,       30,       xxx,   yyy
11, part2,      130310,       50,       xxx,   yyy


So I want to get all the rows with MAX(date_entered) for each partnumber.

Any help is appreciated in creating a SQL query for this.

Thanks
Ian

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 05/17/2013 :  09:11:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
select t1.* from table as t1 inner join
(
select Partnumber, max(date_entered) as date_entered from table group by Partnumber
) as t2 on t1.Partnumber=t2.Partnumber and t1.date_entered =t2.date_entered

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

singularity
Posting Yak Master

151 Posts

Posted - 05/17/2013 :  09:17:10  Show Profile  Reply with Quote

select Id, Partnumber, date_entered, quantity, yadda, yadda2
from
(select Id, Partnumber, date_entered, quantity, yadda, yadda2,
 rank() over (partition by Partnumber order by date_entered desc) as rk
from yourtable) a
where rk = 1
Go to Top of Page

unigee
Starting Member

4 Posts

Posted - 05/17/2013 :  09:43:54  Show Profile  Reply with Quote
I have literally been trying this for days without success and it looks like it has worked.
I just need to sit down and pick it apart so I fully understand the query.

Thank you so much both
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000