To demonstrate the power of SQL, I created a database using T-SQL. I then wrote a large number of queries. Please click below to download the file.
One thing I learned is that window functions can be much faster than subqueries. Please consider this code.
-- Query 19A
-- Previous and next order using subqueries
-- Please note that subqueries are much slower than window functions
SELECT orderdate, empid, custid,orderid,
(SELECT MAX(o2.orderid)
FROM orders AS O2
WHERE o2.orderid < o1.orderid
AND o2.custid = o1.custid
AND o2.custid IS NOT NULL) AS prevorderid,
(SELECT MIN(o3.orderid)
FROM orders AS o3
WHERE o3.orderid > o1.orderid
AND o3.custid = o1.custid
AND o3.custid IS NOT NULL) AS nextorderid
FROM Orders AS o1
WHERE o1.custid IS NOT NULL
ORDER BY o1.custid, o1.orderid;
-- Query 19B
-- Previous and next order using window functions
SELECT orderdate, empid, custid,orderid,
LAG(orderid) OVER(PARTITION BY custid
ORDER BY orderid) AS prevorderid,
LEAD(orderid) OVER(PARTITION BY custid
ORDER BY orderid) AS nextorderid
FROM Orders
WHERE custid IS NOT NULL
ORDER BY custid, orderid;
Copyright © 2022 IRASHARENOW.COM - All Rights Reserved.