Working with Correlated Subqueries

try the below one

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

Select allOpen in new window

:) 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;

Select allOpen in new window

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;

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;

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

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
;

Select allOpen in new window

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

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
;

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.

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;