Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Working with Correlated Subqueries

Posted on 2013-05-10
14
Medium Priority
?
309 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 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 72

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
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

578 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