June 18, 2009 at 6:40 am
Yes, correct also I tried to order by item now is correct! But if you compare the first result set with the last one no the same results.
June 18, 2009 at 6:40 am
That works, thanks Pyay!
June 18, 2009 at 6:41 am
_simon_, try the script in my last post, it should only return 6 rows, rather than 7 rows..
June 18, 2009 at 6:46 am
Hey,
please try this. the union is used to get the last record which wll not be returned in the first query
select a.* from
(select ROW_NUMBER() OVER (ORDER BY item,date) as UID,* from #temp ) a
inner join
(select ROW_NUMBER() OVER (ORDER BY item,date) as UID,* from #temp) b
on (a.UID+1=b.UID and (a.itemb.item or a.price1b.price1 or a.price2b.price2))
union select * from (select top 1 * from
(select ROW_NUMBER() OVER (ORDER BY item,date) as UID,* from #temp ) a
order by UID desc) b
June 18, 2009 at 6:49 am
Both scripts are OK, thank you very much 🙂
June 18, 2009 at 6:49 am
thetodaisies (6/18/2009)
Hey,please try this. the union is used to get the last record which wll not be returned in the first query
select a.* from
(select ROW_NUMBER() OVER (ORDER BY item,date) as UID,* from #temp ) a
inner join
(select ROW_NUMBER() OVER (ORDER BY item,date) as UID,* from #temp) b
on (a.UID+1=b.UID and (a.itemb.item or a.price1b.price1 or a.price2b.price2))
union select * from (select top 1 * from
(select ROW_NUMBER() OVER (ORDER BY item,date) as UID,* from #temp ) a
order by UID desc) b
Now, seems everything ok... still a I don't know exactly ..._simone_ will tell us for the correct answer, but as I can see in your code here seems very compact solution!
Nice code thetodaisies! 😉
June 18, 2009 at 7:01 am
_simon_ (6/18/2009)
Both scripts are OK, thank you very much 🙂
yes, only on the assumption that no price changes will be made on the same day, otherwise both scripts will break 😀
June 18, 2009 at 7:20 am
thetodaisies (6/18/2009)
Hey,please try this. the union is used to get the last record which wll not be returned in the first query
select a.* from
(select ROW_NUMBER() OVER (ORDER BY item,date) as UID,* from #temp ) a
inner join
(select ROW_NUMBER() OVER (ORDER BY item,date) as UID,* from #temp) b
on (a.UID+1=b.UID and (a.itemb.item or a.price1b.price1 or a.price2b.price2))
union select * from (select top 1 * from
(select ROW_NUMBER() OVER (ORDER BY item,date) as UID,* from #temp ) a
order by UID desc) b
I would have done it similarily:
SELECT A.item, A.price1, A.price2, A.date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY item, date) AS UID, * FROM #temp) A
LEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY item, date) AS UID, * FROM #temp) B
ON A.UID+1=B.UID AND A.item=B.item AND A.price1=B.price1 AND A.price2=B.price2
WHERE B.item IS NULL
What we are looking for are rows to which there is no sucessor (next date for same item) with the same price. So we look for rows where the JOIN with this criteria (next date for the same item with the same price) returns no rows. This is the case when B.item IS NULL.
Therefore we simply filter these using "B.item IS NULL" in the WHERE clause.
Best Regards,
Chris Büttner
June 18, 2009 at 7:29 am
June 18, 2009 at 7:39 am
Yeah, nice one, Chris.
June 18, 2009 at 10:30 am
Pyay Nyein (6/18/2009)
Yeah, nice one, Chris.
Well, you guys did the hard part 🙂
Best Regards,
Chris Büttner
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply