There is such a table in the database:

|UserId |DateTo |serviceCode|status|lastDate | |---------|-------------------|-----------|------|-------------------| |116750128|2018-03-11 22:00:00|PE0753.24 |1 |2018-02-12 20:31:43| |116750128|2018-03-29 22:00:00|PE0753.24 |1 |2018-01-29 22:00:00| |116750128|2038-01-19 06:14:07|PE0753.24 |1 |2018-01-30 22:00:00|

You need to select lastDate only for those records whose DateTo is equal to the maximum among DateTo that belong to UserId , serviceCode and status .

Those. in this case, 2018-01-30 22:00:00 should be returned, because DateTo = 2038-01-19 06:14:07 maximum, among UserId = 116750128 and serviceCode = PE0753.24 and status = 1.

I could come up with such a query:

 SELECT p1.lastDate, p1.UserId, p1.serviceCode FROM Users.UserInfo p1 WHERE p1.status= 1 AND p1.UserId>= 116750128 AND p1.UserId< 116750129 AND p1.DateTo= (SELECT max(p2.dateTo) FROM Users.UserInfo p2 WHERE p2.UserId= p1.UserId AND p2.status= p1.status AND p2.serviceCode= p1.serviceCode);

Maybe there is a better way, I do not like the subselect in the WHERE construction. This query takes a long time on the table with about 200 million records.

    2 answers 2

    To implement, we will use the grouping operator ( GROUP BY ), we will group by userId and serviceCode, we will not do it by status, since it is explicitly specified in the request. In SELECT, select the maximum date from the MAX group (p1.lastDate).

     SELECT p1.UserId, p1.serviceCode, MAX(p1.lastDate) maxDate FROM Users.UserInfo p1 WHERE p1.status = 1 AND p1.UserId >= 116750128 AND p1.UserId < 116750129 GROUP BY p1.userId, p1.serviceCode;
    • Not quite right, I don’t need a maximum of p1.lastDate as a result. It is necessary to display the p1.lastDate of the line with the maximum dateTo among the records p2.UserId = p1.UserId AND p2.serviceCode = p1.serviceCode - All_Safe
     SELECT p1.lastDate, p1.UserId, p1.serviceCode FROM Users.UserInfo p1 JOIN (SELECT UserId, serviceCode, max(dateTo) dateTo FROM Users.UserInfo WHERE UserId >= 116750128 AND UserId < 116750129 AND status = 1 GROUP BY UserId, serviceCode ) p2 ON p2.UserId = p1.UserId AND p2.serviceCode = p1.serviceCode AND p1.dateTo = p2.dateTo WHERE p1.status = 1

    It should work quickly if there is an index on UserId (Ideally, on UserId, serviceCode, dateTo if there are a lot of records with one userId, but such an index is very large in itself and can slow down inserts)