Posted by Ted on 08/21/06 16:28

I constructed the following SQL statement by studying the example on
page 392 in Itzik Ben Gan et al.'s "Inside Microsoft SQL Server 2005:
T-SQL Querying"

SELECT s_supplier_code, s_supplier_name FROM suppliers AS S1
WHERE s_supplier_code =
(
SELECT TOP(1) * FROM suppliers AS S2
WHERE S2.s_supplier_code = S1.s_supplier_code
ORDER BY s_supplier_name
)
;

However, it generates the following error.

Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

This isn't a showstopper since the following statement does much the
same thing and it works (even though it is a little less flexible).

SELECT s_supplier_code,MAX(s_supplier_name) FROM suppliers GROUP BY
s_supplier_code;

My code can continue on, but I want to understand why the statement I
constructed by following the example in my book was rejected. Did I
miss something? Or is there an error in the book? Or is there a bug
in SQL Server 2005?

Thanks

Ted

• Next in forum: Re: Problem with SELECT
• Prev in forum: Re: Recommendation for tools converting access databases to SQL
• Thread view: Problem with SELECT

[Reply to this message]