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.