Hi,
I want to find the name and age of the oldest sailor. In a slide I read
it says that this code:
Select S.sname, Max(s.age)
from Sailors S
is Wrong. What is wrong in this code?
and that this code is wrong as well
Select S.sname, Max(s.age)
from Sailors S
Group by s.sname
in this case it says that it's wrong since we will not obtain what we
want.
Can anyone tell me what is wrong in these to blocks of code?
Thanks

>Select S.sname, Max(s.age)
>from Sailors S
>Group by s.sname

This codes tells the database that sailors have multiple ages and you
want to find each sailor's max age. The problem is that you are
defining the range (max) in your SELECT instead of your WHERE clause.

If you want to find the oldest sailor, there are several ways to do it.
Here are two:

1. The easy way:

SELECT TP 1 S.sname, S.age
FRM Sailors S
RDER BY S.age DESC

This will sort the sailors by age in descending order and return the
first (oldest one). If you have a lot of Sailors, this may not be the
most efficient solution as the data has to be sorted first.

2. Another approach is to find the oldest age and get the sailor(s) who
is that age using a subquery:

SELECT S.sname, S.age
FRM Sailors S
WHERE S.age = (SELECT Max(age) FRM Sailors)

Keep in mind that this may return more than one record as you could
have multiple sailors who are the same age.

Good luck,
Mark

So it's the same mistake in both blocks of code? of is it a different
mistake?

Thanks for the answer

scubakiz wrote:
>Select S.sname, Max(s.age)
>from Sailors S
>Group by s.sname
>
This codes tells the database that sailors have multiple ages and you
want to find each sailor's max age. The problem is that you are
defining the range (max) in your SELECT instead of your WHERE clause.

If you want to find the oldest sailor, there are several ways to do it.
Here are two:

1. The easy way:

SELECT TP 1 S.sname, S.age
FRM Sailors S
RDER BY S.age DESC

This will sort the sailors by age in descending order and return the
first (oldest one). If you have a lot of Sailors, this may not be the
most efficient solution as the data has to be sorted first.

2. Another approach is to find the oldest age and get the sailor(s) who
is that age using a subquery:

SELECT S.sname, S.age
FRM Sailors S
WHERE S.age = (SELECT Max(age) FRM Sailors)

Keep in mind that this may return more than one record as you could
have multiple sailors who are the same age.

Good luck,
Mark

Yes, it's the same problem. The first query is incomplete and won't
run on most servers since you need a GRUP BY clause with agragate
functions like MAX. The second query is in the correct form, but is
asking the wrong question.

scubakiz wrote:
Yes, it's the same problem. The first query is incomplete and won't
run on most servers since you need a GRUP BY clause with agragate
functions like MAX. The second query is in the correct form, but is
asking the wrong question.

In addition to the suggested solutions. Here are two more:

Select S.sname, s.age from Sailors S
where not exists (
select 1 from Sailors R where R.age S.age
)

select sname, age from (
select sname, age, rownumber() over (order by age) as rn
from Sailors
) X where rn = 1

Also note that select TP 1 is a vendorspecific feature, and might not
work with your DBMS

HTH
Lennart

Well instead of TP 1 you could do

SELECT S.sname, S.age
FRM Sailors S
RDER BY S.age DESC
LIMIT 1

and couldn't you also do something along the lines of this (may be
vendor specificmay be just plain wrong)

SELECT S.sname, S.age, Max(S.age) AS maxage
FRM Sailors S
HAVING maxage=S.age
GRUP BY S.sname

Donius wrote:
Well instead of TP 1 you could do

SELECT S.sname, S.age
FRM Sailors S
RDER BY S.age DESC
LIMIT 1

isn't LIMIT vendor specific too? (Where's my SQL standard reference?
darn it!)

and couldn't you also do something along the lines of this (may be
vendor specificmay be just plain wrong)

SELECT S.sname, S.age, Max(S.age) AS maxage
FRM Sailors S
HAVING maxage=S.age
GRUP BY S.sname

no, that's wrong. see the post about the problem with
SELECT S.sname, Max(S.age)
FRM Sailors S
GRUP BY S.sname ;

HTH,
ed

Ed Prochak wrote:
Donius wrote:
Well instead of TP 1 you could do

SELECT S.sname, S.age
FRM Sailors S
RDER BY S.age DESC
LIMIT 1

isn't LIMIT vendor specific too? (Where's my SQL standard reference?
darn it!)

I usually end up using:

whenever I'm in doubt. Much easier to find than the place where I left
the standard the last time I used it :-)

/Lennart

[]