1

I am trying to retrieve the top 5 values in a certain column in a mysql database.

I have the following query:

SELECT `dep_aerodrome`,
       Count(`dep_aerodrome`) AS `cnt`
FROM   sectors
WHERE ( `group` = '".$_SESSION['GROUP']."'
        AND ( $bases ) NOT IN dep_aerodrome )
GROUP  BY `dep_aerodrome`
ORDER  BY `cnt` DESC
LIMIT  5

The query contains a WHERE clause and NOT IN to not include airports that are registered as bases.

I am getting the following syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dep_aerodrome) GROUP BY dep_aerodrome ORDER BY cnt DESC LIMIT 5' at line 1

and I cannot figure out where it is coming from. Can anyone help out?

xlecoustillier
  • 16,183
  • 14
  • 60
  • 85
James Gardner
  • 29
  • 6
  • 1
    MySQL does not care about your PHP script, but only about the query it is _actually receiving_ – so please be a little bit smarter and show us the latter one as well, thank you. – CBroe Jul 29 '13 at 09:50

2 Answers2

2

Try this:

SELECT `dep_aerodrome`,
       Count(`dep_aerodrome`) AS `cnt`
FROM   sectors
WHERE `group` = '".$_SESSION['GROUP']."'
        AND ( $bases ) 
        AND dep_aerodrome NOT IN ($some_values)
GROUP  BY `dep_aerodrome`
ORDER  BY `cnt` DESC
LIMIT  5

I think you want to do this like this. $some_values can be in the format of 'value1','value2',... as in:

dep_aerodrome NOT IN ('value1','value2',...)
Mark
  • 8,046
  • 15
  • 48
  • 78
1

The right syntax for using NOT IN is

WHERE column_name NOT IN ('value1','value2',...)

And If you want to match 1 value with multiple columns check this.

Different approach of using IN clause in MySql

Community
  • 1
  • 1
Yogesh Suthar
  • 30,424
  • 18
  • 72
  • 100