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 2012 Forums
 Transact-SQL (2012)
 Question on an alternative query

Author  Topic 

kevie
Starting Member

6 Posts

Posted - 2014-01-21 : 20:19:33
Hello,

I am currently studying on my own and came across an exercise question that has been really bothering me. While I am sure that it will be a snap for many of you, any assistance would be greatly appreciated. I am looking for an alternative to this query without having to rely on partial subqueries that rely on both the outer and inner working together.

Thank you in advance!

select custid, orderid, orderdate, empid
from sales.orders as o1
where orderdate = (select max(o2.orderdate) from sales.orders as o2 where o2.custid = o1.custid)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-21 : 20:53:35
Many way to do it, Here is 2 of them

Method 1

select *
from
(
select custid, orderid, orderdate, empid, rn = row_number() over (partition by custid order by orderdate desc)
from sales.orders
) as d
where rn = 1


Method 2

select o.custid, o.orderid, o.orderdate, o.empid
from sales.orders o
inner join
(
select custid, orderdate = max(orderdate)
from sales.orders
group by custid
) d on o.custid = d.custid and o.orderdate = d.orderdate




KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

kevie
Starting Member

6 Posts

Posted - 2014-01-21 : 21:19:50
Thank you very much!
Go to Top of Page

kevie
Starting Member

6 Posts

Posted - 2014-01-21 : 22:16:05
I hate to ask again so soon but what would be a way to approach this without correlated subqueries? I feel like I am missing something when I approach these questions. If you are able to answer this, would you be able to give a brief explanation on the thought process that you used to approach it differently?

Thank you!

SELECT custid,
ordermonth,
qty,
(SELECT Sum(o2.qty)
FROM sales.custorders AS o2
WHERE o2.custid = o1.custid
AND o2.ordermonth <= o1.ordermonth) AS runqty
FROM sales.custorders AS o1
ORDER BY custid,
ordermonth
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-21 : 22:38:47
you can also use CROSS APPLY to do it

SELECT o1.custid,
o1.ordermonth,
o1.qty,
o2.runqty
FROM sales.customer AS o1
CROSS APPLY
(
SELECT runqty = SUM(x.qty)
FROM sales.custorders x
WHERE x.custid = o1.custid
AND x.ordermonth <= o1.ordermonth
) o2
ORDER BY o1.custid,
o1.ordermonth



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 07:30:08
or simply this if you're on SQL 2012 ( i assume so since you posted in sql 2012 forum)

SELECT custid,
ordermonth,
qty,
Sum(qty) over (partition by custid order by ordermonth rows between unbounded preceding and current row) AS runqty
FROM sales.custorders


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -