Solved

Working with Correlated Subqueries

Posted on 2013-05-10
14
291 Views
Last Modified: 2013-05-29
Hi experts,

SELECT orderid, empid, orderdate
FROM Sales.Orders AS O1
WHERE orderdate =
      (SELECT MAX(orderdate)
       FROM Sales.Orders AS O2
       WHERE O2.empid = O1.empid)
ORDER BY empid, orderdate;

any other way to write this query?
0
Comment
Question by:enrique_aeo
14 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39157546
try the below one

SELECT top 1 orderid, empid, orderdate
FROM Sales.Orders AS O1
ORDER BY orderdate desc, empid ;

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 225 total points
ID: 39157600
are there other ways? yes, here's 2 alternatives:
/* method 1, grouped subquery*/
SELECT
     orderid
    , empid
    , orderdate
FROM Sales.Orders AS O1
INNER JOIN (
            SELECT
              empid
            , MAX(orderdate) as orderdate
            FROM Sales.Orders
            /* if filtering, do it here */
            GROUP BY
              empid
           ) AS O2 O1.empid = O2.empid and O1.orderdate = O2.orderdate
 /* if filtering, do it here also*/
ORDER BY empid
    , orderdate;
    
/* method 2, use row_number() */    
;with
CTE AS (
            SELECT
                  orderid
                , empid
                , orderdate
                , row_number() over (partition by empid order by orderdate DESC) as row_ref
            FROM Sales.Orders AS O1
            /* if filtering, do it here */
       )
SELECT
     orderid
    , empid
    , orderdate
FROM CTE
WHERE CTE.row_ref = 1    
ORDER BY empid
    , orderdate;

Open in new window

0
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 75 total points
ID: 39157975
The second example can also be written without CTE, but in fact there is no difference (I changed as little as possible):
SELECT
     orderid
    , empid
    , orderdate
FROM (
   SELECT
                  orderid
                , empid
                , orderdate
                , row_number() over (partition by empid order by orderdate DESC) as row_ref
            FROM Sales.Orders AS O1
            /* if filtering, do it here */
) CTE
WHERE CTE.row_ref = 1    
ORDER BY empid, orderdate;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39158056
:) yes: 2 methods:, with CTE,  without CTE = 4 variants

Both methods can be used with or without CTE and to round out the variants below is the GROUP BY approach using a CTE.
/* method 1a, grouped subquery as a Common Table Expression*/
;WITH
CTE AS (
        SELECT
          empid
        , MAX(orderdate) as orderdate
        FROM Sales.Orders
        /* if filtering, do it here */
        GROUP BY
          empid
       )
SELECT
     orderid
    , empid
    , orderdate
FROM Sales.Orders AS O1
INNER JOIN CTE AS O2 O1.empid = O2.empid and O1.orderdate = O2.orderdate
 /* if filtering, do it here also*/
ORDER BY empid
    , orderdate;

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 39158138
this is not working
SELECT
     orderid
    , empid
    , orderdate
FROM Sales.Orders AS O1
INNER JOIN (
            SELECT
              empid
            , MAX(orderdate) as orderdate
            FROM Sales.Orders
            /* if filtering, do it here */
            GROUP BY
              empid
           ) AS O2 O1.empid = O2.empid and O1.orderdate = O2.orderdate
 /* if filtering, do it here also*/
ORDER BY empid
    , orderdate;
0
 

Author Comment

by:enrique_aeo
ID: 39158141
this is not working
/* method 1a, grouped subquery as a Common Table Expression*/
;WITH
CTE AS (
        SELECT
          empid
        , MAX(orderdate) as orderdate
        FROM Sales.Orders
        /* if filtering, do it here */
        GROUP BY
          empid
       )
SELECT
     orderid
    , empid
    , orderdate
FROM Sales.Orders AS O1
INNER JOIN CTE AS O2 O1.empid = O2.empid and O1.orderdate = O2.orderdate
 /* if filtering, do it here also*/
ORDER BY empid
    , orderdate;
0
 

Author Comment

by:enrique_aeo
ID: 39158144
--query 1
SELECT orderid, empid, orderdate
FROM Sales.Orders AS O1
WHERE orderdate =
      (SELECT MAX(orderdate)
       FROM Sales.Orders AS O2
       WHERE O2.empid = O1.empid)
ORDER BY empid, orderdate;

11077      1      2008-05-06 00:00:00.000
11073      2      2008-05-05 00:00:00.000
11070      2      2008-05-05 00:00:00.000
11063      3      2008-04-30 00:00:00.000
11076      4      2008-05-06 00:00:00.000
11043      5      2008-04-22 00:00:00.000
11045      6      2008-04-23 00:00:00.000
11074      7      2008-05-06 00:00:00.000
11075      8      2008-05-06 00:00:00.000
11058      9      2008-04-29 00:00:00.000

--2 query 2
;with
CTE AS (
            SELECT
                  orderid
                , empid
                , orderdate
                , row_number() over (partition by empid order by orderdate DESC) as row_ref
            FROM Sales.Orders AS O1
            /* if filtering, do it here */
       )
SELECT
     orderid
    , empid
    , orderdate
FROM CTE
WHERE CTE.row_ref = 1    
ORDER BY empid
    , orderdate;

11077      1      2008-05-06 00:00:00.000
11070      2      2008-05-05 00:00:00.000
11063      3      2008-04-30 00:00:00.000
11076      4      2008-05-06 00:00:00.000
11043      5      2008-04-22 00:00:00.000
11045      6      2008-04-23 00:00:00.000
11074      7      2008-05-06 00:00:00.000
11075      8      2008-05-06 00:00:00.000
11058      9      2008-04-29 00:00:00.000

why the difference?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 225 total points
ID: 39158808
Good question!
The answer is:

Your original method can match to to the same date for an empid, which you see here:

11073      2      2008-05-05 00:00:00.000
11070      2      2008-05-05 00:00:00.000

i.e. empid 2 has two orderids on the same date

Method1 is an exact match to you original - just using a join instead of a subquery in the where clause, I can't spot any reason for it not to be working.


Method 2 (using row_number) will only produce 1 row per "partition " which in the provided example is only considering empid.  So in the case of matching dates this is not an exact match to your original.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39158829
I've re-done method1 here in case something unexpected is in the first one provided
SELECT orderid, empid, orderdate
FROM Sales.Orders AS O1
INNER JOIN (
       SELECT MAX(orderdate) as orderdate
       FROM Sales.Orders
       GROUP BY empid
       ) AS O2 ON O1.empid = O2.empid AND O1.orderdate = O2.orderdate
ORDER BY empid, orderdate
;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39158839
line 15 of my original has a fault which is why it didn't run :( sorry

           ) AS O2 O1.empid = O2.empid and O1.orderdate = O2.orderdate

it is missing "ON"

           ) AS O2 ON O1.empid = O2.empid and O1.orderdate = O2.orderdate

and the same fault re-occurs in method1a, line 17

INNER JOIN CTE AS O2 O1.empid = O2.empid and O1.orderdate = O2.orderdate

also that "ON"

INNER JOIN CTE AS O2 ON O1.empid = O2.empid and O1.orderdate = O2.orderdate

no excuses, just missed it
0
 

Author Comment

by:enrique_aeo
ID: 39166929
this query is not working
SELECT orderid, empid, orderdate
FROM Sales.Orders AS O1
INNER JOIN (
       SELECT MAX(orderdate) as orderdate
       FROM Sales.Orders
       GROUP BY empid
       ) AS O2 ON O1.empid = O2.empid AND O1.orderdate = O2.orderdate
ORDER BY empid, orderdate
;
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39166965
darn: because there is a missing empid column in the subquery

SELECT O1.orderid, O1.empid, O1.orderdate
FROM Sales.Orders AS O1
INNER JOIN (
       SELECT empid, MAX(orderdate) as orderdate
       FROM Sales.Orders
       GROUP BY empid
       ) AS O2 ON O1.empid = O2.empid AND O1.orderdate = O2.orderdate
ORDER BY O1.empid, O1.orderdate
;

also, because both O1 and O2 have some columns of the same name, you must use aliases in the selection list and in the order by list.

btw: if a query errors it is useful to get the error message in most cases.
0
 

Author Comment

by:enrique_aeo
ID: 39166981
I understand that there is another way to do this query

SELECT orderid, empid, orderdate
FROM Sales.Orders AS O1
WHERE orderdate =
      (SELECT MAX(orderdate)
       FROM Sales.Orders AS O2
       WHERE O2.empid = O1.empid)
ORDER BY empid, orderdate;
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 225 total points
ID: 39167015
yes, that is a "correlated subquery" used in the where clause.

it will perform OK for small selections but the inner join should outperform that correlated subquery in most cases.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now