SQL query - WHERE (MAX) AND (= VALUE)

Question

I have a table with about 1000 rows. The rows contain ID, IDUSER, DESCRIPTION and DATE.
I need to get the DESCRIPTION with MAX DATE for a specific IDUSER.

In the example, I need to get the "ASD" DESCRIPTION

ID     IDUSER        DESCRIPTION     DATE
----------------------------------------------
1      1             ASD             4/16/2018   
2      1             QWE             4/10/2018   
3      2             ZXC             3/01/2018  
4      2             BNM             1/26/2018  
5      2             JKL             5/02/2018  
6      3             IOP             3/14/2018  
7      4             RTY             1/13/2018 

I'm trying something like

SELECT DESCRIPTION FROM TABLE WHERE IDUSER = 1 AND DATE = (SELECT MAX(DATE) FROM TABLE)

It's not working, maybe someone can help me with this?

Answer 1

For a specific user, use top (1):

SELECT TOP (1) DESCRIPTION
FROM TABLE
WHERE IDUSER = 1
ORDER BY DATE DESC;

For performance, you want an index on (IDUSER, DATE).

Answer 2

There are several ways to do this. Windowing functions are usually the best and easiest solution in this case:

SELECT *
FROM (
    SELECT DESCRIPTION, 
        ROW_NUMBER() OVER (PARTITION BY IDUSER ORDER BY [DATE] DESC)  AS ROW
    FROM [TABLE]
    WHERE IDUSER = 1
) t
WHERE row = 1

You can also do it via join/group by (works when the partition isn't so clear or you need to show fields from several related tables, and you can know Date is unique for each ID):

SELECT t.DESCRIPTION
FROM
(
    SELECT IDUSER, MAX(DATE) DT
    FROM [Table]
    GROUP BY IDUSER
) base
INNER JOIN [Table] t ON t.IDUSER = base.IDUSER AND t.[DATE] = base.DT
WHERE t.IDUSER = 1

Or a lateral join (like the previous option, but fixes the case of potential duplicate dates):

SELECT u.DESCRIPTION
FROM (SELECT DISTINCT IDUSER FROM [TABLE]) base
OUTER APPLY (
    SELECT TOP 1 DESCRIPTION 
    FROM [TABLE] t 
    WHERE t.IDUSER = base.IDUSER
    ORDER BY t.DATE DESC
) u
WHERE base.IDUSER = 1

The later two options should be less efficient for this exact case, but there are situations where they can work better.