Asked  6 Months ago    Answers:  5   Viewed   31 times

I have the following two tables:

1. Lecturers (LectID, Fname, Lname, degree).
2. Lecturers_Specialization (LectID, Expertise).

I want to find the lecturer with the most Specialization. When I try this, it is not working:

SELECT
  L.LectID, 
  Fname, 
  Lname 
FROM Lecturers L, 
     Lecturers_Specialization S
WHERE L.LectID = S.LectID
AND COUNT(S.Expertise) >= ALL (SELECT
  COUNT(Expertise)
FROM Lecturers_Specialization
GROUP BY LectID);

But when I try this, it works:

SELECT
  L.LectID,
  Fname,
  Lname 
FROM Lecturers L,
     Lecturers_Specialization S
WHERE L.LectID = S.LectID
GROUP BY L.LectID,
         Fname,
         Lname 
HAVING COUNT(S.Expertise) >= ALL (SELECT
  COUNT(Expertise)
FROM Lecturers_Specialization
GROUP BY LectID); 

What is the reason? Thanks.

 Answers

76

WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVING works correctly.

As a rule of thumb, use WHERE before GROUP BY and HAVING after GROUP BY. It is a rather primitive rule, but it is useful in more than 90% of the cases.

While you're at it, you may want to re-write your query using ANSI version of the join:

SELECT  L.LectID, Fname, Lname
FROM Lecturers L
JOIN Lecturers_Specialization S ON L.LectID=S.LectID
GROUP BY L.LectID, Fname, Lname
HAVING COUNT(S.Expertise)>=ALL
(SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID)

This would eliminate WHERE that was used as a theta join condition.

Tuesday, June 1, 2021
 
Null
answered 6 Months ago
35

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

Source

Friday, June 4, 2021
 
Gigamegs
answered 6 Months ago
38

The 2nd answer is your best choice. It takes into account the fact that you could have holes in your ID column. I'd rewrite it as a CTE though instead of a subquery...

;WITH MyCTE AS
(SELECT  *,  
         ROW_NUMBER() OVER (ORDER BY ID) AS row  
FROM     Table)
SELECT   *
FROM     MyCTE
WHERE    row >= @start 
         AND row <= @end
Wednesday, June 16, 2021
 
madphp
answered 6 Months ago
18

Basically, I was using sprintf() to see what kind of bounding coordinates where being computed, and since I couldn't run the query on any place other than PHP (because of the UDF) I was generating another query with prepared statements. The problem was, I wasn't generating the last bound parameter (the kilometers in the distance <= ? clause) and I was fooled by my sprintf() version.

Guess I shouldn't try to code when I'm sleepy. I'm truly sorry for your wasted time, and thank you all!


Just for the sake of completeness, the following returns (correctly!) 873 records, in ~ 0.04 seconds:

SELECT "code",
    geo(38.73311, -9.138707, "geo_latitude", "geo_longitude") AS "distance"
    FROM "pt_postal" WHERE 1 = 1
        AND "geo_latitude" BETWEEN 38.7241268076 AND 38.7420931924
        AND "geo_longitude" BETWEEN -9.15022289523 AND -9.12719110477
        AND "distance" <= 1
    ORDER BY "distance" ASC
LIMIT 2048;
Sunday, August 29, 2021
 
Angolao
answered 3 Months ago
95

It more readable, and more universally accepted to do:

SELECT *
FROM Wherever
WHERE Greeting in ('hello', 'hi', 'hey')

All modern SQL servers optimize your queries, so they're both likely to be changed into the same code that runs on the server, so performance differences will be negligible or non-existent.

Edit:

Apparently the in option is faster, as it evaluates to a binary lookup, whereas the multiple = just evaulates each statement individually.

Sunday, September 19, 2021
 
rojo
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share