Solved

Working with Correlated Subqueries

Posted on 2013-05-10
14
298 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 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 69

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 39
Sql Server group by 10 51
Need help with a Stored Proc on Sql Server 2012 4 35
SQL Sum of items in two tables not equal. 5 49
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 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