SQL subquery , using WHERE & ‘IN’ to filter for specific rows

please use here and copy the code in below to add context to my questions.

As you see, I have a table and within it certain Father’s have the same name, I want to choose the father that has the most dogs for my final list. You can see the entire table in Query #1 , my desired result in Query #2 with respect to Father's returned, but when I am trying to get Father John in Query #3 to only return 1 time but it shows the entire record for Father John with 9 dogs and 10 dogs.

How can I get Query #3 to select only one Father whom has the Max Dogs, and return the rest of the columns as well?

Create tables code:

CREATE TABLE IF NOT EXISTS `table_3` 
(
    `id` int(6) unsigned NOT NULL,
    `Father` varchar(200) NOT NULL,
    `Dogs` varchar(200) NOT NULL,
    PRIMARY KEY (`id`)
);

INSERT INTO `table_3` (`id`,`Father`, `Dogs`) 
VALUES ('1', 'John', '10'),
       ('2', 'John','9'),
       ('3', 'Joe', '4'),
       ('4', 'Jeremy', '4'),
       ('5', 'Jack', '4'),
       ('6', 'NULL', '5');

Query #1

select Father from table_3;

Query #1 OUTPUT:

id  Father  Dogs
1   John    10
2   John    9
3   Joe 4
4   Jeremy  4
5   Jack    4
6   NULL    5

Query #2

select b.Father from (select Father, max(Dogs)
from table_3
group by 1
)b;

Query #2 OUTPUT

Father
Jack
Jeremy
Joe
John
NULL

Query #3

select * from table_3 a
where a.Father in (
select b.Father from (select Father, max(Dogs)
from table_3 
group by 1
)b);

Query #3 Output

id  Father  Dogs
1   John    10
2   John    9
3   Joe 4
4   Jeremy  4
5   Jack    4
6   NULL    5

Desired Output from Query #3

id  Father  Dogs
1   John    10

3   Joe 4
4   Jeremy  4
5   Jack    4
6   NULL    5

Answer

Try this –

SELECT * FROM table_3 A
INNER JOIN( 
    SELECT father,MAX(Dogs) Dogs 
    -- You need to CAST your Dogs column to INT before you apply MAX on this column
    FROM table_3
    GROUP BY Father
)B
ON A.father = B.father
AND A.Dogs = B.Dogs

You can also try with row number if your database permits as below-

SELECT * FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY father ORDER BY Dogs DESC) RN
    FROM table_3
) A WHERE RN = 1

Advertisement

Answer