0

I always struggle with sql subqueries. For example the answer to this question:

select userid,
       my_date,
       ...
from
(
select userid,
      my_Date,
      ...
      max(my_date) over (partition by userid) max_my_date
from   users
)
my_date = max_my_date

Why can't it just be:

select userid,
       my_Date,
       ...
       max(my_date) over (partition by userid) max_my_date
from  users
where 
my_date = max_my_date

I know it is not correct, but after all, the first select only selects something from the result of the second select, plus my_date = max_my_date. In what kind of situation should I think about using this kind of subquery (besides the normal in, exist, etc)?

Community
  • 1
  • 1
ericyoung
  • 621
  • 2
  • 15
  • 21
  • can you give the output of the desired result – polin Feb 07 '13 at 07:29
  • @polin the result itself is not important. When I saw this kind of problem, I have completely no idea where to go. I want to know why two selects are needed. – ericyoung Feb 07 '13 at 07:33
  • @erciyoung: The reason is the order of evaluation (of SELECT queries): `FROM -> WHERE -> SELECT`. You can't use in Where something that hasn't been evaluated yet. – ypercubeᵀᴹ Feb 07 '13 at 07:37

2 Answers2

3

Because the WHERE clause is (logically) processed before SELECT.

The processing order is:

  1. FROM
  2. ON
  3. OUTER
  4. WHERE
  5. GROUP BY
  6. CUBE | ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

(Stolen from here)

As you can see, SELECT actually happens quite late on in the process, after any possible filtering and grouping activity has occurred. So, if you want to use the result of an expression in a SELECT clause, it had better be a SELECT that is part of a subquery (or an (earlier) CTE).

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • @AlexanderFedorenko - blame the blog post I stole from. They don't have a `.` after `10`, which caused that entry to be added to the end of `9.` and SO is doing it's fun thing with list numbering. – Damien_The_Unbeliever Feb 07 '13 at 07:42
  • You can steal from [this answer](http://stackoverflow.com/questions/6545664/using-case-expression-column-in-where-clause/6545685#6545685) next time. I've left out the not-standard `OUTER APPLY` and `TOP`. – ypercubeᵀᴹ Feb 07 '13 at 07:43
  • @Damien_The_Unbeliever Seems I got it from your answer. can you give some scenarios that subqueries should be used? In other words, if i saw a problem like that, intuitively, I will think of using subquery. – ericyoung Feb 07 '13 at 08:04
0

max() is a GROUP function in SQl thats the reason of not getting correct answer in second query.

in first query it first retrieve the max value then comparing the max value in the table.

Saifuddin Sarker
  • 853
  • 3
  • 8
  • 26