CODEWITHSUNDEEP
sql
user1384831
user1384831

Reputation: 219

Is this a valid SQL query?

This is just an example, I'm doing something similar that is going to grab thousands of records. 

SELECT * FROM 
(SELECT * FROM zoodb) As TblA 
INNER JOIN 
(SELECT animal_ID, Max(checkup_year) AS latest_checkup FROM TblA GROUP BY animal_ID) as TblB 
ON (TblA.animal_ID = TblB.animal_ID) AND (TblA.checkup_year = TblB.latest_checkup)

Basically in this, I want to grab the records only for the latest checkup year

Upvotes: 0

Views: 255

Answers (4)

Lynn Langit
Lynn Langit

Reputation: 4060

Also the query optimizer prefers inner joins to nested select statements, so you'll get a more optional execution plan

Upvotes: 0

Simon
Simon

Reputation: 506

aF.'s answer is correct, but I thought I'd propose a solution that expresses your intention a little more clearly:

select *
from zoodb as TblA
where latest_checkup = (
    select max(latest_checkup)
    from zoodb
    where animal_id = TblA.animal_id);

The lack of the [inner join (subselect)] also means that it won't get too messy if you need to add additional constraints in the future.

Upvotes: 0

Umair
Umair

Reputation: 3243

SELECT * FROM 
(SELECT * FROM zoodb) As TblA 
....

There is no need for this sub query. The FROM in the outer query can handle it!

SELECT * 
FROM zoodb AS TblA
...

The rest looks fine!

Edit:

As aF pointed out you cannot reference TblA from the inner join subquery (See aF's answer).

Upvotes: 0

aF.
aF.

Reputation: 66697

Your query is not valid.

But you can do it this way:

SELECT * FROM zoodb z
INNER JOIN (SELECT animal_ID, Max(checkup_year) AS latest_checkup
            FROM zoodb
            GROUP BY animal_ID) aux ON aux.animal_ID = z.animal_ID AND aux.latest_checkup = z.checkup_year

Upvotes: 3

Related Questions