Get TOP(1) Date and Time of Status table

Question

SELECT RIGHT(LEFT(a.JobOrders, 16), 3) as OrderSuffix, COUNT(1) as TotalCount
FROM Orders a, Status b
WHERE a.JobOrders = b.JobOrders
    AND b.Status = 'Finished'
GROUP BY RIGHT(LEFT(a.JobOrders, 16), 3)
ORDER BY TotalCount Desc

Is there a way to include in that SELECT query the TOP (1) Date and TIME fields of table Status where RIGHT(LEFT(a.JobOrders, 16), 3) = <3 letter value>?

What I am currently doing is I get first the distinct values, OrderSuffix. Put it on a datatable. For each row, I am sending an SQL where RIGHT(LEFT(a.JobOrders, 16), 3) = <3 letter value> to get the TOP (1) Date and Time.

Possible duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Vladimir Baranov, Jun 04 '18 at 04:38
Either aggregate function or APPLY operation might do this, but it's not clear in the question how the 3 letter value impacts the whole query. Perhaps some sample data and results would illustrate this for us. — Joel Coehoorn, Jun 04 '18 at 04:39

score 1 · Accepted Answer · answered Jun 04 '18 at 04:50

For details of two common approaches how to do the top-n-per-group query see the Get top 1 row of each group question.

The query below uses the ROW_NUMBER approach.

WITH
CTE
AS
(
    SELECT 
        RIGHT(LEFT(a.JobOrders, 16), 3) as OrderSuffix
        ,COUNT(1) OVER (PARTITION BY RIGHT(LEFT(a.JobOrders, 16), 3)) as TotalCount
        ,b.Date
        ,b.Time
        ,ROW_NUMBER() OVER 
            (PARTITION BY RIGHT(LEFT(a.JobOrders, 16), 3) 
            ORDER BY b.Date DESC, b.Time DESC) as rn
    FROM
        Orders a
        INNER JOIN Status b ON a.JobOrders = b.JobOrders
    WHERE
        b.Status = 'Finished'
)
SELECT
    OrderSuffix
    ,TotalCount
    ,Date
    ,Time
FROM CTE
WHERE rn = 1
ORDER BY TotalCount Desc;

Get TOP(1) Date and Time of Status table

1 Answers1