ANY and ALL

G

Guest

I am writing a subquery using the all statement and it seems to be giving a
problem.

SELECT EID, Salary, Department
FROM tblAccounts
WHERE Salary > ALL
(SELECT Salary
FROM tblAccounts
WHERE Department = "Sales");

The problem is that the subquery is not taken in to account. So the answer
shows for all departments not just for the sales department. This also
happens when I use ANY instead of ALL.

Please help.
 
C

Chris M

Darren said:
I am writing a subquery using the all statement and it seems to be giving a
problem.

SELECT EID, Salary, Department
FROM tblAccounts
WHERE Salary > ALL
(SELECT Salary
FROM tblAccounts
WHERE Department = "Sales");

The problem is that the subquery is not taken in to account. So the answer
shows for all departments not just for the sales department. This also
happens when I use ANY instead of ALL.

Please help.
Click to expand...

Darren, sorry, probably just me being dense this morning, but what exactly
is the result you want to achieve from that query?

Cheers,

Chris.
 
G

Guest

Hi Chris,

I am trying to use the ALL to pull back the person that earns the highest
salary in the Sales department.
 
G

Guest

Try using Top 1 with order by salary

SELECT Top 1 EID, Salary, Department
FROM tblAccounts
Order By Salary Desc
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck
 
G

Guest

Sorry, I forgeot the where

SELECT Top 1 EID, Salary, Department
FROM tblAccounts
WHERE Department = "Sales"
Order By Salary Desc

Will return the top EID In the Sales Department
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck
 
G

Guest

I need to use the ANY and All functions as this is an exsample that i need to
teach to others in our office
 
C

Chris M

Chris M said:
Hi Chris,

I am trying to use the ALL to pull back the person that earns the highest
salary in the Sales department.
Click to expand...

OK, so your sub-query is giving you all the salaries in the sales department
to compare against. What are you doing in your main query to make sure you
only return the highest SALES department salary and not just the highest
overall salary?
 
J

John Spencer

Unless I misunderstand the use of ALL in this context, then the proposed
query will never return any records. Since the salary has to be greater
than all the salaries in the department - an impossibility.

I would think that the OP would have to use a Max or TOP subquery to get the
results and an equals operator.

....
WHERE Department = "Sales" And
Salary= (SELECT Max(Salary)
FROM tblAccounts
WHERE Department = "Sales")
 
M

Michel Walsh

Hi,

You should use

Salary >= ALL( ...)


not a strict >.


Note that if some Salary values are NULL, as reported by SELECT Salary FROM
tblAccounts WHERE Department="Sales", then the comparison fails (it return
unknown, not True).


Using


Salary > ANY( ... )


would return all records except the one with Salary=(SELECT MIN(salary) FROM
.... WHERE ... )


It is generally preferable to use MIN and MAX to skip the NULL problem, as
in:


WHERE Salary = (SELECT MAX(salary) FROM ... WHERE.... )

instead of

WHERE Salary >= ALL(SELECT salary FROM ... WHERE.... )





Hoping it may help,
Vanderghast, Access MVP
 
C

Chris2

Darren said:
I am writing a subquery using the all statement and it seems to be giving a
problem.

SELECT EID, Salary, Department
FROM tblAccounts
WHERE Salary > ALL
(SELECT Salary
FROM tblAccounts
WHERE Department = "Sales");

The problem is that the subquery is not taken in to account. So the answer
shows for all departments not just for the sales department. This also
happens when I use ANY instead of ALL.

Please help.
Click to expand...

Darren,

Aircode:

SELECT EID
,Salary
,Department
FROM tblAccounts
WHERE Department = "Sales"
AND Salary > ALL
(SELECT Salary
FROM tblAccounts
WHERE Department = "Sales");

Note: If there are two or more "largest" salaries, I am thinking that
this query won't return any rows.


Sincerely,

Chris O.
 
M

Marshall Barton

Darren said:
I am writing a subquery using the all statement and it seems to be giving a
problem.

SELECT EID, Salary, Department
FROM tblAccounts
WHERE Salary > ALL
(SELECT Salary
FROM tblAccounts
WHERE Department = "Sales");

The problem is that the subquery is not taken in to account. So the answer
shows for all departments not just for the sales department. This also
happens when I use ANY instead of ALL.
Click to expand...


In addition to all the other comments, you should to use a
table alias name in the subquery:

SELECT EID, Salary, Department
FROM tblAccounts
WHERE Salary >= ALL
(SELECT X.Salary
FROM tblAccounts As X
WHERE X.Department = "Sales");

But, as has already been pointed out, this is a lousy
example. If anyone leaves the class thinking this a good
way to do it, you've done them a disservice.
 
C

Chris2

John Spencer said:
Unless I misunderstand the use of ALL in this context, then the proposed
query will never return any records. Since the salary has to be greater
than all the salaries in the department - an impossibility.
Click to expand...

John Spencer,

You're right, as the test table and sample data below show . :(

(And you're also right about there being more efficient ways of
getting this info, but the OP wanted the "ANY or ALL" keywords to be
in use . . .)


I think I should have written:

SELECT A1.EID
,A1.Salary
,A1.Department
FROM tblAccounts AS A1
WHERE A1.Department = "Sales"
AND A1.Salary > ALL
(SELECT Salary
FROM tblAccounts AS A01
WHERE A01.Department = "Sales"
AND A01.EID <> A1.EID);

Table:

CREATE TABLE tblAccounts
(EID AUTOINCREMENT
,SALARY CURRENCY
,Department TEXT(10)
,CONSTRAINT pk_tblAccounts PRIMARY KEY (EID)
)

Sample Data
1, 15000, BldEngrs
2, 16000, BldEngrs
3, 17000, BldEngrs
4, 50000, HR
5, 60000, HR
6, 30000, Sales
7, 40000, Sales
8, 50000, Sales
9, 60000, Sales
10, 50000, Sales


Output:

9, 60000, Sales

Ok, so the above new query does appear to work.

However, like I mentioned originally, if there are ties:

New Row appended in:
11, 60000, Sales

Then no rows are returned.


Sincerely,

Chris O.
 
J

John Spencer

Yes, that may be true (re OP's request); however, what they are asking to do
cannot (as far as I know) be done using ANY or ALL.
 
C

Chris M

John Spencer said:
Yes, that may be true (re OP's request); however, what they are asking to
do cannot (as far as I know) be done using ANY or ALL.
Click to expand...

How about this?


SELECT A1.EID
,A1.Salary
,A1.Department
FROM tblAccounts AS A1
WHERE A1.Department = "Sales"
AND A1.Salary > ALL
(SELECT Salary
FROM tblAccounts AS A01
WHERE A01.Department = "Sales"
AND A01.Salary <> A1.Salary);

Chris.
 
J

John Spencer

Glad I said "as far as I know". That looks as if it would work. Still
seems the hard way to accomplish this particular goal, but the technique may
be useful in the future.

Thanks for the example.
 
C

Chris M

Glad I said "as far as I know". That looks as if it would work. Still
seems the hard way to accomplish this particular goal, but the technique
may be useful in the future.
Click to expand...

John, just between you and me, I think that might have been a homework
assignment.

'Use ANY and ALL as you've been taught to select the highest salary from the
'SALES' department in the following table...'

;-)

Chris.
 
C

Chris M

Chris2 said:
Chris M.

Yeah, it did sound a bit contrived.


Oh, and a little testing on my part shows that changing:


to

AND A1.Salary => ALL

also brings back ties.
Click to expand...

Yea, I found that worked too, but I couldn't quite explain why. To my mind,
'= ALL...' so by association '>=ALL' doesn't really make sense.

I haven't come across ANY and ALL before, but I'm assuming '> ALL' means the
test value must be greater than ALL the values returned by the subquery, and
ANY means the test value must be greater than at least one of the values
returned by the subquery.

Cheers,

Chris.
 
C

Chris2

Chris M said:
John, just between you and me, I think that might have been a homework
assignment.

'Use ANY and ALL as you've been taught to select the highest salary from the
'SALES' department in the following table...'

;-)

Chris.
Click to expand...

Chris M.

Yeah, it did sound a bit contrived.


Oh, and a little testing on my part shows that changing:

to

AND A1.Salary => ALL

also brings back ties.


Sincerely,

Chris O.
 
M

Michel Walsh

Hi,


Exactly. As example:

WHERE 60 <= ALL (SELECT note FROM courses WHERE studentID='Joe' )

would translate the fact that for a given student, all the courses have a
note >= 60. Someone could also express the same idea with:

WHERE 60 <= (SELECT MIN(note) FROM courses WHERE studentID='Joe' )

but that implies you mentally reversed the logic of the given rule, which
can lead to maintenance problem if the logic is complex.

Another example is about to know if all the tests had been made for a given
procedure: isTestDone is Boolean:

WHERE -1 = ALL (SELECT isTestDone FROM Tests WHERE procedureID= 4004 )

Without using the ALL quantifier... what would it be? Maybe:

WHERE NOT EXISTS( SELECT * FROM tests WHERE procedureID = 4004 AND NOT
isTestDone )

but the following won't work:

WHERE 0=(SELECT COUNT(*) FROM tests WHERE procedureID=4004 AND NOT
isTestDone )

since if there is no record, there is no row to hold the count(*). If you
want a solution based on 0 = COUNT(*), that becomes, like:

WHERE 0=(SELECT COUNT(*) FROM ( SELECT * FROM tests WHERE procedureID
= 4004 AND NOT isTestDone ) )




Hoping it may help,
Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select and input data from a list 1
an unusual query? 2
Invalid Parameters 2
Help with logic on a query.. 12
Query Too Complex But it is pretty simple. 1
Help with query for invoice report 2
Finding the date of last pay rise 8
help me 2

Top