In the following sections, some other advanced SQL techniques will be introduced:
Quite often it is necessary to find the first records based on some criteria. For example, let's take the car_portal
database; suppose it is required to find the first advertisement for each car_id
in the advertisement
table.
Grouping can help in this case. It will require a subquery to implement the logic:
SELECT advertisement_id, advertisement_date, adv.car_id, seller_account_id FROM car_portal_app.advertisement adv INNER JOIN ( SELECT car_id, min(advertisement_date) min_date FROM car_portal_app.advertisement GROUP BY car_id ) first ON adv.car_id=first.car_id AND adv.advertisement_date = first.min_date;...