SQL finding person entry has the most dogs

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 > (
        s开发者_JS百科elect p3.id ...etc

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

---

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

---

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

---

Presuming DOGS is a column on PERSONS, using the rank() analytic function is the cleanest approach. Ordering by DOGS descending means the person with the highest number of dogs has a rank of 1.

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. Depending on the business logic this is usually a desirable outcome. If you really must have a single row you need stronger partitioning condition or finer ordering criteria. Alternatively the analytic row_number() is guaranteed to return one row, if you're not fussed which of the tied records you get.

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