New to SQL Server Sub Queries - Hopefully an easy one

Question

I just cant work this out in a single query. Table TblQcProduction has a record for each operator on a Production order.

I need to find out who the last operator was that reported against the production order.

SELECT  IDX, ProdOrder, Operator
FROM dbo.TblQCProduction

Returns:

IDX     ProdOrder  Operator
8050745 325184     13012
8050746 325184     13035
8050747 325184     13036
8050748 325186     13005
8050749 325186     13038
8050750 325187     13022
8050751 325191     13022
8050752 325191     13035

I need the Output to be (Return the Operator for the MAX IDX by prodOrder):

IDX     ProdOrder Operator
8050747 325184    13036
8050749 325186    13038
8050750 325187    13022
8050752 325191    13035

I have had no joy with grouping or sub-queries - i have the feeling there will be a very simple answer.

score 2 · Answer 1 · answered Sep 26 '13 at 11:44

2

You need to use ROW_NUMBER() OVER

select IDX,ProdOrder,Operator 

from 
(
select IDX,ProdOrder,Operator,
       ROW_NUMBER() OVER 
         (PARTITION BY ProdOrder ORDER BY IDX DESC) as rn
from TblQCProduction
) as T
WHERE RN=1;

SQLFiddle demo

answered Sep 26 '13 at 11:44

valex

23,966
7
43
60

score 1 · Answer 2 · answered Sep 26 '13 at 11:44

Try this one -

DECLARE @temp TABLE
(
    IDX INT, ProdOrder INT, Operator INT
)

INSERT INTO @temp (IDX, ProdOrder, Operator)
VALUES 
    (8050745, 325184, 13012),
    (8050746, 325184, 13035),
    (8050747, 325184, 13036),
    (8050748, 325186, 13005),
    (8050749, 325186, 13038),
    (8050750, 325187, 13022),
    (8050751, 325191, 13022),
    (8050752, 325191, 13035)

SELECT t.IDX, t.ProdOrder, t.Operator
FROM ( 
    SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY ProdOrder ORDER BY IDX DESC) 
    FROM @temp
) t
WHERE t.rn = 1

Output -

IDX         ProdOrder   Operator
----------- ----------- -----------
8050747     325184      13036
8050749     325186      13038
8050750     325187      13022
8050752     325191      13035

+1 I used Pieter's Solution The luddites here are on SQL2000 — user2819192, Sep 26 '13 at 13:43

score 0 · Answer 3 · answered Sep 26 '13 at 11:45

;WITH MyCTE AS
(
    SELECT IDX,
           ProdOrder,
           Operator
           ROW_NUMBER() OVER (PARTITION BY ProdOrder ORDER BY IDX DESC) AS RowNum
    FROM   TblQCProduction
)

SELECT IDX,
       ProdOrder,
       Operator 
FROM MyCTE 
WHERE RowNum = 1

score 0 · Answer 4 · answered Sep 26 '13 at 11:57

0

I like joins:

SELECT midx, t.prodorder, tt.operator 
FROM  (
  SELECT MAX(idx) AS midx, prodorder
  FROM dbo.TblQCProduction GROUP BY prodorder
) t LEFT JOIN dbo.TblQCProduction tt ON t.midx=tt.idx

answered Sep 26 '13 at 11:57

Pieter B

1,874
10
22

Pieter - You got it Works great. The luddites here are on SQL2000 – user2819192 Sep 26 '13 at 13:42
As I said I like joins, in SQL Server versions that support it, I think that the "partition by" syntax is less costly. – Pieter B Sep 26 '13 at 14:01

New to SQL Server Sub Queries - Hopefully an easy one

4 Answers4