SQL finding person entry has the most dogs

Question

person(id, dogs)

how would I find the person with the most dogs

select *
from person p1
where p1.id in (
    select p2.id
    from person p2
    where p1.id = p2.id
    having count(dogs > (
        select p3.id ...etc

am I on the right track or will this not work? thanks for taking a look

Answer 1

Order them and take the first row:

SELECT *
FROM person
ORDER BY dogs DESC
LIMIT 1

Note that the way to fetch only the first row varies depending on your database.

Since you didn't specify your database this is just an example. For SQL Server you would use SELECT TOP 1 instead.

Now I see that you have updated your question. For Oracle you can use ROWNUM.

SELECT *
FROM
(
    SELECT *
    FROM person
    ORDER BY dogs DESC
) WHERE rownum = 1

Answer 2

How about this?

select *
from person
where dogs = (select max(dogs) from person)

Probably not the most efficient way, but that will give you the person (or persons) that have the greatest dog count. If you just want one of them, you can do this:

select top 1 
from person
order by dogs desc

Answer 3

Presuming DOGS is a column on PERSONS, using the RANK() analytic function is the cleanest approach:

select * from
   ( select p.*
            rank() over (order by p.dogs desc) as rnk
     from person p )
where rnk = 1
/

RANK() has the advantage that if two people tie for the most number of dogs both rows will be returned.

Oracle has a powerful range of analytic functions. Find out more.