Subqueries: What am I doing fundamentally wrong?

Question

I thought that selecting values from a subquery in SQL would only yield values from that subset until I found a very nasty bug in code. Here is an example of my problem.

I'm selecting the rows that contain the latest(max) function by date. This correctly returns 4 rows with the latest check in of each function.
```
select *, max(date) from cm where file_id == 5933 group by function_id;
```

file_id   function_id   date             value  max(date)
5933      64807         1407941297       1      1407941297
5933      64808         1407941297       11     1407941297
5933      895175        1306072348              1306072348
5933      895178        1363182349              1363182349

When selecting only the value from the subset above, it returns function values from previous dates, i.e. rows that don't belong in the subset above. You can see the result below where the dates are older than in the first subset.
```
select temp.function_id, temp.date, temp.value
from (select *, max(date)
      from cm
      where file_id 5933
      group by function_id) as temp;
```

function_id   date          value

64807         1306072348    1   &lt-outdated row, not in first subset
64808         1306072348    17  &lt-outdated row, not in first subset
895175        1306072348        
895178        1363182349

What am I doing fundamentally wrong? Shouldn't selects performed on subqueries only return possible results from those subqueries?

I think the problem is that you group your rowset by function_id, while you are keeping the rows' other fields. Try adding the other, non-aggregated (max / count / min ) fields you your GROUP BY. If they are needless, just don"t keep them in your SELECT statement. — jpicaude, Feb 20 '15 at 21:28

score 1 · Answer 1 · answered Feb 21 '15 at 09:26

1

SQLite allows you to use MAX() to select the row to be returned by a GROUP BY, but this works only if the MAX() is actually computed. When you throw the max(date) column away, this no longer works.

In this case, you actually want to use the date value, so you can just keep the MAX():

SELECT function_id,
       max(date) AS date,
       value
FROM cm
WHERE file_id = 5933
GROUP BY function_id

answered Feb 21 '15 at 09:26

CL.

173,858
17
217
259

So the subquery with the group by returns all of the rows, not only the max date rows? I'm asking why it's inconsistent, I thought querying a subquery would only return the results from that subquery. – danihodovic Feb 21 '15 at 11:24
The subquery returns _random_ rows, one from each group. – CL. Feb 21 '15 at 15:07
So it doesn't return the row with the max date? – danihodovic Feb 21 '15 at 16:08
1

It is not guaranteed to do that if the max() column is not actually returned. – CL. Feb 21 '15 at 22:08

score 0 · Answer 2 · answered Feb 20 '15 at 21:27

0

You seem to be missing the fact that your subquery is returning ALL rows for the given file_id. If you want to restrict your subquery to recs with the most recent date, then you need to restrict it with a WHERE NOT EXISTS clause to check that no more recent records exist for the given condition.

answered Feb 20 '15 at 21:27

But the subquery seems to only return 4 rows? Give me an example of the WHERE NOT EXISTS clause. – danihodovic Feb 20 '15 at 22:15

score 0 · Accepted Answer · edited May 23 '17 at 12:28

0

Perhaps my question was not formulated correctly, but this post had the solutions I was essentially looking for:

https://stackoverflow.com/a/123481/2966951

https://stackoverflow.com/a/121435/2966951

Filtering out the most recent row was my problem. I was surprised that selecting from a subquery with a max value could yield anything other than that value.

edited May 23 '17 at 12:28

Community

1
1

answered Feb 25 '15 at 19:26

danihodovic

1,151
3
18
28

Subqueries: What am I doing fundamentally wrong?

3 Answers3