How to use" HAVING "and "ORDER BY" clause in sql

Question

I have sql query like shown below the sql server says that their is error in HAVING and ORDER bY is their any error with syntax near HAVING and ORDER BY, can anyone help.

SELECT Id,
       Name1,
       ZipCode,
       StreetName,
       StreetNumber,
       State1,
       Lat,
       Lng,
       Keyword,
       ( 6371 * ACOS( COS( (12.925432/57.2958) ) * COS(  (Lat/57.2958)  ) * COS( ( Lng/57.2958 ) - (77.5940171/57.2958) ) + SIN( 12.925432/57.2958 ) * SIN(  Lat/57.2958  ) ) ) AS distance 
  FROM Business_Details 
  HAVING (distance < 1.5) and (Keyword like '%plumber%')  
  ORDER BY distance  ;

Binary Worrier · Accepted Answer · 2010-08-31T11:33:19.063

8

Use where here instead of having.

having is useful for narrowing conditions on aggregate values.
where is useful for narrowing conditions on un-aggregated data.

Update
SQL Server is not MySQL, what works on one ...

May just work on the other
May need to be tweaked slightly to work
May need to be completely re-enginerred before it will work.

This should be what you need

SELECT Id, 
       Name1, 
       ZipCode, 
       StreetName, 
       StreetNumber, 
       State1, 
       Lat, 
       Lng, 
       Keyword, 
       ( 6371 * ACOS( COS( (12.925432/57.2958) ) * COS(  (Lat/57.2958)  ) * COS( ( Lng/57.2958 ) - (77.5940171/57.2958) ) + SIN( 12.925432/57.2958 ) * SIN(  Lat/57.2958  ) ) ) AS distance  
  FROM Business_Details  
  where (Keyword like '%plumber%')  
  and ( 6371 * ACOS( COS( (12.925432/57.2958) ) * COS(  (Lat/57.2958)  ) * COS( ( Lng/57.2958 ) - (77.5940171/57.2958) ) + SIN( 12.925432/57.2958 ) * SIN(  Lat/57.2958  ) ) ) < 1.5
  ORDER BY ( 6371 * ACOS( COS( (12.925432/57.2958) ) * COS(  (Lat/57.2958)  ) * COS( ( Lng/57.2958 ) - (77.5940171/57.2958) ) + SIN( 12.925432/57.2958 ) * SIN(  Lat/57.2958  ) ) ) ;

edited Aug 31 '10 at 11:33

answered Aug 31 '10 at 10:36

Binary Worrier

50,774
20
136
184

This isn't the cause of the error which is the main point of this question – Daniel Renshaw Aug 31 '10 at 10:39
3

Having is the cause of the error, you cannot use it without adding a group by. It seems clear from the SQL that the user is trying to use the Having clause in the context of the Where clause. – Wes Price Aug 31 '10 at 10:47
2

Actually, you can use a HAVING without a GROUP BY clause. This SQL runs fine: SELECT COUNT(*) FROM sys.databases HAVING MAX(database_id) < 100 – Neil Knight Aug 31 '10 at 11:02
Thanks for the reply In this particular article code.google.com/apis/maps/articles/phpsqlsearch.html they have used the following query with having and order by clause they are using mysql server in that i am using sql server 2005 does it make any difference in query SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20; – mahesh Aug 31 '10 at 11:07
1

Yes, it will make a difference. – Neil Knight Aug 31 '10 at 11:10
@Daniel Renshaw. When running (very similar) to the above on SQL2005 "Msg 8121, Level 16, State 1, Line 1 Column 'tableName.columnName' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause." – Binary Worrier Aug 31 '10 at 11:23
@Binary Worrier: my mistake, I thought `HAVING` was interchangable with `WHERE` when no `GROUP BY` was specified; I've just checked this and found that you're right and I was wrong :-) This means that there are *two* errors in the original SQL (having vs where and referencing a computed result in the having/where clause) – Daniel Renshaw Aug 31 '10 at 11:27
@Ardman: That works becase the column database_id is implicitly used in the aggregate function `count`. if it were `select *` instead of `select count(*)` it wouldn't work. – Binary Worrier Aug 31 '10 at 11:28
@Daniel Renshaw: Well spotted, hadn't noticed `distance` – Binary Worrier Aug 31 '10 at 11:31
@Binary Worrier: Which proves my point about using the HAVING clause without a GROUP BY :o) – Neil Knight Aug 31 '10 at 12:37

score 2 · Answer 2 · edited May 23 '17 at 10:32

Everyone has already mentioned that HAVING is for aggregate data, like SUM and MIN and you can put those two conditions in your WHERE clause.

For what it's worth (I cannot test right now) and to hopefully address your actual question, it has been my experience that you can not use a column alias in the HAVING clause, and must explicitly rewrite the equation for the column there instead of the alias.

See this question for more details.

score 2 · Answer 3 · answered Aug 31 '10 at 10:39

2

HAVING is generally used in conjunction with a GROUP BY statement. HAVING is like the WHERE for the resulting groups.

answered Aug 31 '10 at 10:39

hqrsie

413
5
10

In this particular article http://code.google.com/apis/maps/articles/phpsqlsearch.html they have used the following query with having and order by clause they are using mysql server in that i am using sql server 2005 does it make any difference in query SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20; – mahesh Aug 31 '10 at 11:05

score 1 · Answer 4 · answered Aug 31 '10 at 10:36

1

SELECT * from   
 (SELECT Id,
   Name1,
   ZipCode,
   StreetName,
   StreetNumber,
   State1,
   Lat,
   Lng,
   Keyword,
   ( 6371 * ACOS( COS( (12.925432/57.2958) ) * COS(  (Lat/57.2958)  ) * COS( ( Lng/57.2958 ) - (77.5940171/57.2958) ) + SIN( 12.925432/57.2958 ) * SIN(  Lat/57.2958  ) ) ) AS distance 
FROM Business_Details 
)
WHERE (distance < 1.5) and (Keyword like '%plumber%')  
 ORDER BY distance  ;

answered Aug 31 '10 at 10:36

Michael Pakhantsov

24,855
6
60
59

Thanks for the reply In this particular article code.google.com/apis/maps/articles/phpsqlsearch.html they have used the following query with having and order by clause they are using mysql server in that i am using sql server 2005 does it make any difference in query SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20; – mahesh Aug 31 '10 at 11:06

How to use" HAVING "and "ORDER BY" clause in sql

4 Answers4

Linked