CODEWITHSUNDEEP
sqlsql-server-cesubquerysyntax-error
Joe Whitehead
Joe Whitehead

Reputation: 49

SQL Subqueries throwing an error

I am trying to run this query

    SELECT        Name, Position, Salary
FROM            Employee
WHERE        (Salary =
                             (SELECT        MAX(Salary)
                               FROM            Employee))

However whenever I run it I get the following error

Error Source: SQL Server Compact ADO.NET Data Provider
Error Message: There was an error parsing the query. [Token line number = 1, Token line offset = 69, Token in error = SELECT]

I'm wondering if this is me not undertsanding how subqueries work although my tutor assures me that the statement I am trying to run is correct, could anyone shed some light on this situation, thanks.

Upvotes: 3

Views: 1970

Answers (2)

sgeddes
sgeddes

Reputation: 62861

This should work for SQL Server:

SELECT Name, Position, Salary
FROM Employee E
   JOIN ( SELECT Max(Salary) as Sal FROM Employee ) M  ON E.Salary = M.Sal

Here is the SQL Fiddle.

--EDIT

It sounds like you are using SQL Server CE which does not fully support nested sub queries. Try something like this instead using the IN keyword:

SELECT e.Name, e.Position, e.Salary
FROM Employee e
WHERE e.Salary IN (SELECT MAX(Salary) as sal 
                   FROM Employee)

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Compact edition has obviously several limitations. You can try this one:

SELECT e.Name, e.Position, e.Salary
FROM Employee e
  LEFT JOIN Employee s
    ON s.Salary > e.Salary
WHERE s.Salary IS NULL ;

and this:

SELECT e.Name, e.Position, e.Salary
FROM Employee e
WHERE NOT EXISTS
      ( SELECT *
        FROM Employee s
        WHERE s.Salary > e.Salary
      ) ;

and this:

SELECT e.Name, e.Position, e.Salary
FROM Employee e
WHERE e.Salary IN 
      ( SELECT MAX(s.Salary)
        FROM Employee s
      ) ;

and this (maybe CE does not allow GROUP BY in subqueries/derived tables, or group by on the whole table):

SELECT e.Name, e.Position, e.Salary
FROM Employee e
WHERE e.Salary = 
      ( SELECT TOP (1) s.Salary
        FROM Employee s
        ORDER BY s.Salary DESC
      ) ;

Upvotes: 1

Related Questions