Posted by Tony Rogerson on 08/22/06 07:15

You have incorrectly reproduced the query from Itziks book (just checked
myself)

Check back again, you will see that you have put * (all columns) you should
have used a single column key in Itziks example it was OrderId.

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

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


"Ted" <r.ted.byers@rogers.com> wrote in message
news:1156177703.807874.29310@b28g2000cwb.googlegroups.com...
>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
>

[Back to original message]