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>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? <<
This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.|||--CELKO-- wrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
Quote:
Originally Posted by
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? <<
>
This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.
I think I feel a headache coming on!
The publisher is Microsoft Press. It is for SQL Server 2005 and I AM
RUNNING SQL Server 2005! You'd think they'd run sample code from their
own book against their own SQL Server.
Cheers,
Ted
Someone pass me the super strength Motrin! :-(|||* Ted wrote, On 21.08.2006 17:28:
Quote:
Originally Posted by
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.
What happens if you replace
select top(1) *
with this?
select top(1) some_column_name
Boa
[snip]|||Ted wrote:
Quote:
Originally Posted by
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
)
;
>
Ted,
The example in Listing 7-1 is different from your query, and the
example works (and it makes perfect sense to me). I am not sure I
understand what you were trying to accomplish. Note that:
1. Listing 7-1 uses SELECT TOP(1) OrderId, as opposed to your top(1) *
2. Listing 7-1 uses different columns to filter the result set
(OrderId) and to correlate the subquery (EmployeeId). Using one and the
same solumn s_supplier_code in both cases makes little sense to me.
Good luck!|||boa wrote:
Quote:
Originally Posted by
* Ted wrote, On 21.08.2006 17:28:
Quote:
Originally Posted by
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.
>
>
What happens if you replace
select top(1) *
with this?
select top(1) some_column_name
>
Nothing changes. The same error is generated.
Thanks
Ted|||Since this is MS-only
Quote:
Originally Posted by
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.
Joe,
Oracle and DB2 also provide proprietary features for this purpose, and
those features are also widely used. Looks like something is missing in
the standard, right?|||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.comwrote in message
news:1156177703.807874.29310@.b28g2000cwb.googlegro ups.com...
Quote:
Originally Posted by
>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
>
Quote:
Originally Posted by
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>
As ever you are talking utter rubbish.
It takes 2 or 3 or sometimes more releases to deprecate and then discontinue
a feature, actually - thats better than the standard SQL cycle where you
only get 4 year was it celko? And you don't get any warning there either
unless you subscribe and pay the fees....
Quote:
Originally Posted by
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.
>
So, just how would you using standard SQL do the equivelant of this without
using a 'scratch tape' or relying on the application to dig you out of the
featureless ansi sql standard?
-- Get last 3 most recent sales...
select ...
from sales
where orderid in (
select top 3 orderid
from sales
order by orderdate desc
)
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.netwrote in message
news:1156180035.849964.264800@.b28g2000cwb.googlegr oups.com...
Quote:
Originally Posted by
Quote:
Originally Posted by
Quote:
Originally Posted by
>>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? <<
>
This is just how God punishes people for using proprietary code :)
Since this is MS-only
syntax, they can do anything they wish from release to release. Ergo,
you are screwed and live at their whim without recourse to a
higherauthority.
>
Drop an email to the publisher, so they can fix it in the book and use
the Standard. MAX() syntax you provided. Since it is Standard SQL, MS
has to keep it the same from release to release.
>|||Thanks Tony and Alexander
Thanks to your posts, I now understand what I missed and I now have it
working.
Thanks
Ted
