1

Topic: Gets rid from SELECT in expression WHERE

There is such table in a DB:
|UserId |DateTo |serviceCode|status|lastDate |
|116750128|2018-03-11 10:00:00 PM |PE0753.24 |1 |2018-02-12 8:31:43 PM |
|116750128|2018-03-29 10:00:00 PM |PE0753.24 |1 |2018-01-29 10:00:00 PM |
|116750128|2038-01-19 6:14:07 AM |PE0753.24 |1 |2018-01-30 10:00:00 PM |
It is necessary to select lastDate only at those records at which ` DateTo ` it is equal to a maximum among ` DateTo ` which concern to ` UserId `, ` serviceCode ` and ` status `.
I.e. in this case should return ` 2018-01-30 22:00:00 `, since DateTo=2038-01-19 6:14:07 AM to a maximum, among UserId=116750128 and serviceCode=PE0753.24 and status=1.
Could invent such request:

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);

Can eat a method better, it is not pleasant  in construction WHERE. The given request long fulfills on the table with order of 200 million records.

2

Re: Gets rid from SELECT in expression WHERE

sergey-iv3;
Show DDL tables and the request plan.
As the variant, request can be rewritten so:

SELECT
p1.lastDate, p1.UserId, p1.serviceCode
FROM Users. UserInfo p1
WHERE
p1.status = 1
AND p1.UserId> = 116750128
AND p1.UserId <116750129
AND NOT EXISTS (SELECT NULL FROM Users. UserInfo p2
WHERE p2.UserId = p1.UserId
AND p2.status = p1.status
AND p2.serviceCode = p1.serviceCode
AND 2.DateTo> p1.DateTo)