How to fetch data from database without using a sub-query in following scenario?-CodePudding

I had to get the town name and state from database which deployed the first reciever(a device to sense sharks). I used the following query with a subquery computing MIN of deployed dates:

SELECT t.town,t.state,r.deployed FROM receiver as r
inner join township as t
on r.location=t.tid
where deployed=(select min(deployed) from receiver);

I got following result which is correct: Result from query

I tried to do the same thing by using having clause i.e without the subquery as following:

SELECT t.town,t.state,r.deployed FROM receiver as r
inner join township as t
on r.location=t.tid
having r.deployed=min(deployed);

But I got no rows from this query. My question is whats the problem with second query? Am I not using the min function properly in the query? What's the right way to write this query without subquery so that I can get the result just as I got from the former query?

CodePudding user response：

If you don't care about ties, we can use a limit query here:

SELECT t.town, t.state, r.deployed
FROM receiver AS r
INNER JOIN township AS t
    ON r.location = t.tid
ORDER BY r.deployed
LIMIT 1;

If you do want all ties, then RANK() is another option, but that would require a subquery:

WITH cte AS (
    SELECT t.town, t.state, r.deployed, RANK() OVER (ORDER BY r.deployed) rnk
    FROM receiver AS r
    INNER JOIN township AS t
        ON r.location = t.tid
)

SELECT town, state, deployed
FROM cte
WHERE rnk = 1;

Note that some other databases (besides MySQL) do support a QUALIFY clause, which actually would let us use RANK() without a formal subquery:

SELECT t.town, t.state, r.deployed
FROM receiver AS r
INNER JOIN township AS t
    ON r.location = t.tid
QUALIFY RANK() OVER (ORDER BY r.deployed) = 1;

CodePudding user response：

You cannot use the field deployed both as an aggregate MIN(deployed) AND a non-aggregate r.deployed at the same time. The first query works because the subquery searches and finds ONE value, and then the main query compares ALL values, one by one, to that one found value to find the right one. These are two separate actions, both (in theory) going through their individual entire datasets. You cannot combine these two very different actions in one query without subquery.