Display Most recent ID with matching conditions

Question

With this query I want to display only the most recent entry that have a similar PersonPosting and PostDate, in order to display the ID that is the most recent entry.

Table:

  Post_ID  |  PersonPosting  |  PostDate  |  StartTime

      1            2            2019-09-24     12:30pm
      2            2            2019-09-24     12:33pm
      3            2            2019-09-25     12:30pm
      4            2            2019-09-25     12:33pm
      5            1            2019-09-26     2:30pm
      6            1            2019-09-26     2:31pm
      7            1            2019-09-26     2:32pm
      8            1            2019-09-26     2:33pm

I've tried adjusting the subquery to return the StartTime instead

SELECT

etl.Post_ID, etl.PersonPosting, etl.PostDate, etl.StartTime,

(SELECT MAX (post.PostTime) from [log].[Posts] post2 
WHERE post2.PostDate = etl.PostDate AND etl2.PersonPosting = etl.PersonPosting) as Post_ID

FROM log.Posts post
WHERE 

group by PostDate, PersonPosting, Post_ID, StartTime
Order By 
etl.PostDate DESC, 
PersonPosting ASC;

Expected Result:

  Post_ID  |  PersonPosting  |  PostDate  |  StartTime

      2             2          2019-09-24     12:33pm
      4             2          2019-09-25     12:33pm
      8             1          2019-09-26     2:33pm

Error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

score 0 · Answer 1 · answered Sep 26 '19 at 20:26

Assuming that startTime is really a time, you can use a correlated subquery:

select p.*
from logs.post p
where p.StartTime = (select max(p2.StartTime)
                     from logs.post p2
                     where p2.PersonPosting = p.PersonPosting and
                           p2.PostDate = p.PostDate 
                    );

For performance, you want an index on logs.post(PersonPosting, PostDate, StartTime).

score 0 · Accepted Answer · answered Sep 26 '19 at 20:33

You could use a correlated subquery with a NOT EXISTS condition to ensure that no other record exists on the same person and date with a greater start time:

SELECT p.*
FROM log.Posts p
WHERE NOT EXISTS (
    SELECT 1
    FROM posts p1
    WHERE  
        p1.PersonPosting = p1.PersonPosting
        AND p1.PostDate = p.PostDate 
        AND p1.StartTime > p.StartTime
)

In MySQL 8.0, ROW_NUMBER() gives you a shorter syntax:

SELECT *
FROM (
    SELECT
        p.*,
        ROW_NUMBER() OVER(PARTITION BY PersonPosting, PostDate ORDER BY StartTime DESC) rn
    FROM log.Posts p
) x
WHERE rn = 1

Display Most recent ID with matching conditions

2 Answers2