NANA NANA - 6 months ago 9
SQL Question

Can not group some of the selected columns

I'm trying to pull up the selected columns for each UserID the last Date

DT
he made a purchase.

So I think this is wrong because I can't group by
T_Purchased_USD_AMT, T_Attack , T_Steals


DT - Date played

SELECT UserID, MAX(DT), T_Purchased_USD_AMT, T_Attack , T_Steals
FROM analyst_test
WHERE T_PurchasedItem > 0
GROUP BY UserID;


I only know basic SQL so simple is better. Thanks!

Answer

If you're using SQL Server, or any other dbms that supports window functions, you can use ROW_NUMBER:

WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY DT DESC)
FROM analyst_test
WHERE T_PurchasedItem > 0 
)
SELECT 
    UserID, DT, T_Purchased_USD_AMT, T_Attack , T_Steals
FROM Cte
WHERE rn = 1
Comments