Why use ALL? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Why use ALL?

Author

Topic

rahamanf
Starting Member

15 Posts

Posted - 2008-03-22 : 12:32:10

I am asked to write a SELECT statement that answers this question: which invoices have a PaymentTotal that’s greater than the median PaymentTotal for all paid invoices? Return the InvoiceNumber and InvoiceTotal for each invoice.

So, My query is:
SELECT InvoiceNumber, InvoiceTotal FROM INVOICES
WHERE PaymentTotaL IN
(SELECT TOP 50 PERCENT PaymentTotal FROM INVOICES)

It works fine. The problem is - the question also says - "Use the ALL keyword in the WHERE clause"

I dont see the justification of using ALL here, sure I am missing it.

Any idea?
Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-22 : 13:24:13

ALL is not required for correct results but simply part of the homework requirements.

>>which invoices have a PaymentTotal that’s greater than the median PaymentTotal for all paid invoices?

Your query will include the median value where the requirement is ">" and not sure if the "paid invoices" part means excluding partial payments or not. also, you can't assume the order in your "top 50 percent" subquery.

where paymentTotal > ALL
(select top 50 percent paymentTotal from...WHERE <paid> ORDER BY paymentTotal Desc)

Be One with the Optimizer
TG

rahamanf
Starting Member

15 Posts

Posted - 2008-03-23 : 11:02:58

Thanks for your response, TG.

The problem is not with anything else,
but only with ALL.

If I use ALL, it returns no rows.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-23 : 12:23:52

sorry, I had the order by direction wrong. should be ASC.

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-24 : 12:07:21

According to BOL the sub query is a restricted SELECT statement, in which the ORDER BY clause, the COMPUTE clause, and the INTO keyword are not allowed. So, order by is not allowed. Why it doesn;t generate an error, I'm not sure. But maybe something like this would work:

SELECT InvoiceNumber, InvoiceTotal 
FROM INVOICES
WHERE PaymentTotaL > ALL (SELECT (MAX(PaymentTotal) + MIN(PaymentTotal)) / $2.0 FROM INVOICES)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-24 : 13:06:57

Also from Books Online:

quote:
The SELECT query of a subquery is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-24 : 13:49:16

quote:
Originally posted by TG

Also from Books Online:
quote:
The SELECT query of a subquery is always enclosed in parentheses. It cannot include a COMPUTE or FOR BROWSE clause, and may only include an ORDER BY clause when a TOP clause is also specified.

Sorry TG, I'm not sure if I understand why you posted that quote from BOL. The "subquery" used with the ALL keyword is different from a "regular" subquery. Perhaps, I am just not making the connection. If you can provide some context, that would probably help me to understand your point better.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-24 : 15:49:17

Sorry for the lack of explanation.

First let me say that I think that a subquery used with ALL is a "regular" subquery. The only restriction is that it must return 1 column and of the same datatype as that of the comparison scalar_expression.

I think that Books Online's definition of subquery as expressed in the "ALL" reference
http://msdn2.microsoft.com/en-us/library/ms178543.aspx

is identical to the definition as expressed in "Search Condition"
http://msdn2.microsoft.com/en-us/library/ms173545.aspx

and incorrectly says ORDER BY is not allowed. It should say, "Order by is not allowed unless the TOP clause is also specified"

The quote I included in my previous post is from "Subquery fundamentals"
http://msdn2.microsoft.com/en-us/library/aa213252(SQL.80).aspx

Besides, a simple test supports that a subquery with TOP and ORDER BY works for:
where <col> > ALL (subquery)

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-24 : 16:22:30

TG, Thanks for the clarification!

I went back and looked at my sample and I had my sorting backwards (DESC vs ASC). SO, it appears that the subquery's are the same.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-24 : 16:24:04

you made the same mistake I did (or you copied my mistake)

the middle statement should have the ORDER BY ASC.

which of course make sense, right? we are looking for any rows greater than ALL of the BOTTOM 50%

Be One with the Optimizer
TG

rahamanf
Starting Member

15 Posts

Posted - 2008-03-25 : 01:05:02

Thanks for all your helpful discussions, but I just couldn't get it to work.t..dumb me.
If I use ALL, it doesn't return any row at all...

.

SELECT InvoiceNumber, InvoiceTotal FROM INVOICES
WHERE PaymentTotaL > ALL
(SELECT TOP 50 PERCENT PaymentTotal FROM INVOICES order by PaymentTotal Desc)?

rahamanf
Starting Member

15 Posts

Posted - 2008-03-25 : 01:10:58

oh..instead of desc, if i use asc - it returns 50 percent rows - nowi'll verify if they are correct rows.

rahamanf
Starting Member

15 Posts

Posted - 2008-03-25 : 01:19:11

YUP, GREAT! THANKS TO BOTH OF YOU.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources