SQL help please!

[Cybertronic]

I'm stuck on my SQL at the moment, what I'm trying to do is to get the name of the pet that has visited the vets on the most occasions.

Here's the current query I've created at the moment:

SELECT petname AS "Pet name"
FROM pet
WHERE petid = (SELECT Max(count(visit.petid))
FROM visit
GROUP BY petid);

The problem in this query, the petID 6 shows up 4 times but it displays the name of petID 4, could someone please help me how can I display the name of the pet that has visited on most occasions please?

I'm using Oracle SQL*Plus if that helps

 

[Haircut]

There are numerous ways to do this in Oracle, here is one way - probably not the most effiecient though.

SELECT *
FROM
(SELECT p.petname AS "Pet name"
       ,count(*)
 FROM pet p
     ,visit v
 WHERE p.petid = v.petid
 GROUP BY petname
 ORDER BY count(*) DESC
)
WHERE ROWNUM = 1

Haven't tested this so I make no guarantees as to whether it will work!

 

[Cybertronic]

Thanks Haircut

I'm not aware of the Count(*) as I don't think I've learnt it yet

Is it possible to do this query without using the ROWNUM statement?

 

[Andre]

Slightly different - in MSSQL

SELECT TOP 1 p.petname AS [Pet name]
       ,count(*)
 FROM pet AS p
 INNER JOIN visit AS v     ON p.petid = v.pedit
 GROUP BY petname
 ORDER BY count(*) DESC