CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
user3542348
user3542348

Reputation: 31

What is the best way to select rows with maximum value?

I have come across a task, I managed to complete the objective but the solution I got is not optimum, I need more optimum solution. I have used normal Sub Queries May be Correlated Sub Query can solve this better.

This is the table i made 

SELECT custid, 
       count(DISTINCT bid) AS Total 
FROM   loan 
GROUP  BY custid;

The output of this is like:-

enter image description here

What I want is the custid having maximum Total.

One way to do it is using Order by Total DESC LIMIT 1 but this will give only 1 result.

What I did is 

SELECT custid 
FROM   (SELECT custid, 
               count(DISTINCT bid) AS Total 
        FROM   loan 
        GROUP  BY custid) c1 
WHERE  total = (SELECT max(Total) 
                FROM   (SELECT custid, 
                               count(DISTINCT bid) AS Total 
                        FROM   loan 
                        GROUP  BY custid) c2)

This gives me correct result that is

enter image description here

What I want to do is reduce the code, because here I am writing the same thing again. I know there must be a simpler way to do it. Maybe a correlated query.

Looking for some good answers. This is basically to clear my concepts only

Sorry, if it is noob question. I am a noob to SQL.

Upvotes: 3

Views: 367

Answers (3)

Dan
Dan

Reputation: 10171

I haven't tried this in mysql, but in the sql language I'm using it is fine to use a aggregation function without a group by so something like this

select custid, total, max(total) as maxtotal
from (select custid, count(distinct bid) as total
      from loan
      group by custid) c1;

would tag on every line both the individual customer total and the table wide max total, and you'd just have to filter on the ones that where the total was equal to the max total. That would give you a final answer of something like this:

select custid
from (select custid, count(distinct bid) as total
from loan
group by custid) c1
where total = max(total);

Upvotes: 0

Eric
Eric

Reputation: 24880

Here is another way:

select * from loan
where custid = 
(
    select custid_count.custid from
    (select custid, count(distinct bid) as Total from loan group by custid order by Total desc) as custid_count
    order by custid_count.Total desc
    limit 1

);

First find the custid with max count, then query all rows which match the custid,

Upvotes: 0

Eric
Eric

Reputation: 24880

After understand what OP want with @Ravinder 's tip,
I guess build in mysql function GROUP_CONCAT is what you need, sql is:

select custid_count.Total, GROUP_CONCAT(custid_count.custid order by custid_count.custid asc SEPARATOR ',') as custids from
(select custid, count(distinct bid) as Total from loan group by custid order by Total desc) as custid_count
group by custid_count.Total
order by custid_count.Total desc
limit 1;

the result column custids is the max ids concated by ',' ,after the query, you need to split custids by ',' and convert each substring to number type you need,

Upvotes: 2

Related Questions