sql group by function

问题:

I need to only get the line with the highest transactiontime per productId. So In this case I need to get the first line and all the other lines with productid 224 should be gone. How can I fix this? Now I group by NQ but there are multiple lines because the NQ changes by every transaction. I also can not take a SUM because then it would add up everything instead of taking the NQ at that certain transaction time. Help is much appreciated

SELECT NQ, ProductId, Product, Warehouse, ProductType, MAX(Transactiontime) as 'TransactionTime'
FROM @MaxTime
GROUP BY NQ, Productid, Product, Warehouse, ProductType 
ORDER BY ProductId

回答1:

You could do:

SELECT  MT.NQ, MT.ProductId, MT.Product, MT.Warehouse, MT.ProductType, MT.Transactiontime
FROM    @MaxTime MT
INNER JOIN (
                SELECT  ProductId
                ,       MAX(Transactiontime) AS 'TransactionTime'
                FROM    @MaxTime
                GROUP BY Productid
            ) GR
        ON  MT.ProductId = GR.ProductId
        AND MT.TransactionTime = GR.TransactionTime
ORDER BY MT.ProductId

回答2:

As you said, grouping by NQ is what's messing your result up, because each row has a different value.

If you can assume that latest TransactionTime has lowest NQ value (which is true with the sample data you provided) you can just remove NQ from the group by and select min(NQ) instead of plain NQ.

SELECT  min(NQ), ProductId, Product, Warehouse, ProductType, MAX(Transactiontime) as 'TransactionTime'
FROM    @MaxTime
GROUP BY Productid, Product, Warehouse, ProductType 
ORDER BY ProductId

I'm afraid that would be assuming too much, and if that's true you will have to use @HoneyBadger answer

回答3:

You could use a CTE, so that you ensure you get the NQ from the same row as the TransactionTime, e.g.:

;WITH Data AS (
    SELECT      NQ, 
                ProductId, 
                Product, 
                Warehouse, 
                ProductType, 
                TransactionTime,
                ROW_NUMBER() OVER (PARTITION BY Productid, Product, Warehouse, ProductType ORDER BY TransactionTime DESC) AS rn

    FROM        @MaxTime
)
SELECT      NQ, 
            ProductId, 
            Product, 
            Warehouse, 
            ProductType, 
            TransactionTime

FROM        Data

WHERE       rn = 1

ORDER BY    ProductId