Select query keep rows with latest with 4 fields

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

SQL Server 2008 Forums

Transact-SQL (2008)

Select query keep rows with latest with 4 fields

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-04 : 16:19:42

Is it posoble to only keep rows.
Based on the 4 field combination (yearnum, QtrNum, Pctcompletedate, taskid)

only keep the row which is latest / dated and delete the remaining rows. My aim is to keep that QtrNum's latest row only. based on above 4 field combination.

Declare @Sample table (taskid int, Pct int, PctCompleteDate datetime, QtrNum int, MonNum Int, YearNum int)
insert @Sample
select 168, 10,'24-Jun-2011', 2, 6, 2011 union all
select 168, 20,'25-Jul-2011', 3, 7, 2011 union all
select 168, 29,'26-Aug-2011', 3, 8, 2011 union all
select 169, 10,'24-Jun-2011', 2, 6, 2011 union all
select 169, 29,'26-Aug-2011', 3, 8, 2011 union all
select 169, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 10,'24-Jun-2011', 2, 6, 2011 union all
select 170, 29,'26-Aug-2011', 3, 8, 2011

thank you very much for the helpful info.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-04 : 17:14:35

You mean something like this?


Declare @Sample table (taskid int, Pct int, PctCompleteDate datetime, QtrNum int, MonNum Int, YearNum int)
insert @Sample
select 168, 10,'24-Jun-2011', 2, 6, 2011 union all
select 168, 20,'25-Jul-2011', 3, 7, 2011 union all
select 168, 29,'26-Aug-2011', 3, 8, 2011 union all
select 169, 10,'24-Jun-2011', 2, 6, 2011 union all
select 169, 29,'26-Aug-2011', 3, 8, 2011 union all
select 169, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 10,'24-Jun-2011', 2, 6, 2011 union all
select 170, 29,'26-Aug-2011', 3, 8, 2011

delete s
from   @sample s
left outer join (
       select top 1
              taskid
              ,Pctcompletedate
              ,QtrNum
              ,YearNum
       from   @sample
       order by yearnum desc, 
              QtrNum desc, 
              Pctcompletedate desc, 
              taskid desc
              ) d
       on d.yearnum = s.yearnum
       and d.QtrNum = s.QtrNum
       and d.Pctcompletedate = s.Pctcompletedate
       and d.taskid = s.taskid
where  d.yearnum is null

select * from @sample

OUTPUT:
taskid      Pct         PctCompleteDate         QtrNum      MonNum      YearNum
----------- ----------- ----------------------- ----------- ----------- -----------
170         29          2011-08-26 00:00:00.000 3           8           2011

Be One with the Optimizer
TG

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-04 : 17:26:42

Thks TG,

I should get one from each taskid.

i have taskid's: 168, 169, 170

from task 168 i should get 1 row.

same with taskid 169 and 170...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-04 : 17:50:10

[code]
Declare @Sample table (taskid int, Pct int, PctCompleteDate datetime, QtrNum int, MonNum Int, YearNum int)
insert @Sample
select 168, 10,'24-Jun-2011', 2, 6, 2011 union all
select 168, 20,'25-Jul-2011', 3, 7, 2011 union all
select 168, 29,'26-Aug-2011', 3, 8, 2011 union all
select 169, 10,'24-Jun-2011', 2, 6, 2011 union all
select 169, 29,'26-Aug-2011', 3, 8, 2011 union all
select 169, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 10,'24-Jun-2011', 2, 6, 2011 union all
select 170, 29,'26-Aug-2011', 3, 8, 2011

delete s
from (
select taskid
,Pctcompletedate
,QtrNum
,YearNum
,rn = row_number() over (partition by taskid
order by yearnum desc,
QtrNum desc,
Pctcompletedate desc)
from @sample
) d
join @sample s
on s.yearnum = d.yearnum
and s.QtrNum = d.QtrNum
and s.Pctcompletedate = d.Pctcompletedate
and s.taskid = d.taskid
where d.rn > 1

select * from @sample

OUTPUT:
taskid Pct PctCompleteDate QtrNum MonNum YearNum
----------- ----------- ----------------------- ----------- ----------- -----------
168 29 2011-08-26 00:00:00.000 3 8 2011
169 29 2011-08-26 00:00:00.000 3 8 2011
170 29 2011-08-26 00:00:00.000 3 8 2011
[/code]

Be One with the Optimizer
TG

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-04 : 18:58:13

Thank you TG, It worked....

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-04 : 19:49:20

Sorry TG, the qTrNum 2 related rows are not appearing.
In each Qtr/Year/pctcompletedate based on taskid,
one record has to appear.

In case of task id 168 these two records should appear, one row representing each quarter:


168         10          2011-06-24 00:00:00.000 2			6			2011 

168         29          2011-08-26 00:00:00.000 3           8           2011

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-04 : 22:08:23

Have you attempted to figure out how these queries work? Now that you see the technique can't you make the necessary modification? Next time instead of doling out the requirements a bit at a time why don't you type out the all the desired results based on your sample data?

>>Qtr/Year/pctcompletedate
Just add the additional columns to the PARTITION BY clause and remove them from the ORDER BY clause in the ROW_NUMBER() function.

Good luck - I know you can do it

Be One with the Optimizer
TG

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-05 : 05:28:11

Thank you very much TG, it worked...
Declare @Sample table (taskid int, Pct int, PctCompleteDate datetime, QtrNum int, MonNum Int, YearNum int)
insert @Sample
select 168, 10,'24-Jun-2011', 2, 6, 2011 union all
select 168, 10,'30-Jun-2011', 2, 6, 2011 union all
select 168, 20,'25-Jul-2011', 3, 7, 2011 union all
select 168, 29,'26-Sep-2011', 3, 8, 2011 union all
select 169, 10,'24-Jun-2011', 2, 6, 2011 union all
select 169, 29,'26-Aug-2011', 3, 8, 2011 union all
select 169, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 10,'24-Jun-2011', 2, 6, 2011 union all
select 170, 29,'26-Aug-2011', 3, 8, 2011

delete s
from (
select taskid
,Pctcompletedate
,QtrNum
,YearNum
,rn = row_number() over (partition by taskid,QtrNum
order by yearnum desc,
Pctcompletedate desc)
from @sample
) d
join @sample s
on s.yearnum = d.yearnum
and s.QtrNum = d.QtrNum
and s.Pctcompletedate = d.Pctcompletedate
and s.taskid = d.taskid
where d.rn > 1

select * from @sample

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-05 : 09:07:04

You're welcome.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources