CODEWITHSUNDEEP
sqlsql-server
MiniDr
MiniDr

Reputation: 301

Get TOP(1) Date and Time of Status table

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.

Upvotes: 0

Views: 64

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32675

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;

Upvotes: 1

Related Questions