Problem with SELECT

Giganews Newsgroups
Subject: Problem with SELECT
Posted by:  Ted (r.ted.bye…@rogers.com)
Date: 21 Aug 2006

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

Replies