Question on an alternative query

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]

kevie
Starting Member

6 Posts

Posted - 2014-01-21 : 21:19:50

Thank you very much!

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

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]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources