?
Solved

Working with Correlated Subqueries

Posted on 2013-05-10
14
Medium Priority
?
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 900 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 70

Assisted Solution

by:Qlemo
Qlemo earned 300 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 49

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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 900 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 49

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 49

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 49

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 49

Accepted Solution

by:
PortletPaul earned 900 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

764 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