MySQL Forums
Forum List  »  Newbie

Re: New to forum, is this efficient SQL?
Posted by: Peter Brawley
Date: February 02, 2016 12:59PM

Correlated subqueries in the Select list are sometimes necessary but aren't terribly efficient.

In your query, aggregation is hidden inside the correlated subqueries, so Group By is unnecessary in the outer query.

Simplest way to find min and max cruise_dates for codes like 'j%':

select cruise_code, min(cruise_date) as earliest, max(cruise_date) as latest
from mobsea_cruises
where cruise_code like 'j%'
group by cruise_code;

If you also need cruise_id in the result, add it to the Select list and to the Group By clause. If there are multiple cruise_id values for some min or max values, they'll show up in the result list.

Options: ReplyQuote


Subject
Written By
Posted
Re: New to forum, is this efficient SQL?
February 02, 2016 12:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.