Why this sample can not be rewritten using a **WHERE clause**?

`SELECT StudentId FROM EXAM_RESULT GROUP BY StudentID HAVING MIN(Mark) = 5`

asked March 23rd 20 at 18:56

3 answers

answered on

Solution

To understand what the student Mark=5 -- minimal, you need to group values, suddenly the minimum (in addition to checking for-there or not).

That is all your condition to check, we have to group, WHERE, just check -- there is a value or not and displays the students that have

WHERE the same engine is used at the time of sampling, it is not physically possible to know the minimum value

HAVING already working with aggregation functions, i.e., we have grouped, feel the need function (the minimum value in your case), and HAVING already given with the condition that count

That is all your condition to check, we have to group, WHERE, just check -- there is a value or not and displays the students that have

WHERE the same engine is used at the time of sampling, it is not physically possible to know the minimum value

HAVING already working with aggregation functions, i.e., we have grouped, feel the need function (the minimum value in your case), and HAVING already given with the condition that count

answered on

Solution

From the documentation:

The main difference between WHERE clause from the HAVING clause is that the WHERE clause selects rows first, and then groups them and computes the aggregate functions (so it selects the rows to compute aggregates), whereas HAVING selects group rows after groups and calculate aggregate functions. As a result, the WHERE clause must not contain aggregate functions; does not make sense to use aggregate functions to define the rows to calculate the aggregate functions. The HAVING clause, on the contrary, always contains aggregate functions. (Strictly speaking, you can write a HAVING clause without using units, but this is rarely useful. The same condition can run more efficiently at the WHERE stage.)

answered on

Solution

And you can rewrite... but in the form of a twisted cascade of nested selects:

- internal dump will be grouped StudentId and MIN(Mark)

- external will selectit StudentId with a where clause

)

- internal dump will be grouped StudentId and MIN(Mark)

- external will selectit StudentId with a where clause

)

Find more questions by tags SQL