开发者

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

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 '%plu开发者_StackOverflow中文版mber%')  
  ORDER BY distance  ; 


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 ...

  1. May just work on the other
  2. May need to be tweaked slightly to work
  3. 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  ) ) ) ;  


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


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.


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  ; 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜