Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Selecting data based off last date

Author  Topic 

unigee
Starting Member

4 Posts

Posted - 2013-05-17 : 09:07:45
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

22864 Posts

Posted - 2013-05-17 : 09:11:56
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

153 Posts

Posted - 2013-05-17 : 09:17:10
[code]
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
[/code]
Go to Top of Page

unigee
Starting Member

4 Posts

Posted - 2013-05-17 : 09:43:54
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
   

- Advertisement -