CODEWITHSUNDEEP
mysqlcountsql-order-by
Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25755

ORDER BY does not work if COUNT is used

I have a table with following content

loan_application
+----+---------+
| id | user_id |
+----+---------+
|  1 |      10 |
|  2 |      10 |
|  3 |      10 |
+----+---------+

I want to fetch 3rd record only if there are 3 records available, in this case i want id 3 and total count must be 3, here is what i expect

+--------------+----+
| COUNT(la.id) | id |
+--------------+----+
|            3 |  3 |
+--------------+----+

Here is the query i tried.

SELECT COUNT(la.id), la.id FROM loan_application la HAVING COUNT(la.id) = 3 ORDER BY la.id DESC;

However this gives me following result

+--------------+----+
| COUNT(la.id) | id |
+--------------+----+
|            3 |  1 |
+--------------+----+

The problem is that it returns id 1 even if i use order by id descending, whereas i am expecting the id to have value of 3, where am i going wrong ?

Thanks.

Upvotes: 2

Views: 695

Answers (3)

1000111
1000111

Reputation: 13519

You are selecting la.id along with an aggregated function (COUNT). So after iterating the first record the la.id is selected but the count goes on. So in this case you will get the first la.id not the last. In order to get the last la.id you need to use the max function on that field.

Here's the updated query:

SELECT
    COUNT(la.id),
    MAX(la.id)
FROM
    loan_application la
GROUP BY user_id
HAVING
    COUNT(la.id) = 3

N:B: You are using COUNT without a GROUP BY Function. So this particular aggregated function is applied to the whole table.

Upvotes: 1

Shadow
Shadow

Reputation: 34285

When you have a group by function (in this instance count()) in the select list without a group by clause, then mysql will return a single record only with the function applied to the whole table.

Mysql under certain configuration settings allow you to include fields in the select loist which are not in the group by clause, nor are aggregated. Mysql pretty much picks up the 1st value it encounters while scanning the data as a value for such fields, in your case the value 1 for id.

If you want to fetch the record where id=count of records within the table, then I would use the following query:

select *
from loan_application
join (select count(*) as numrows from loan_application) t
where id=t.numrows and t.numrows=3

However, this implies that the values within the id field are continuous and there are no gaps.

Upvotes: 1

rdn87
rdn87

Reputation: 724

In your case u can use this query: 

SELECT COUNT(la.id), max(la.id) FROM loan_application la 
GROUP BY user_id

I try your table in my db MySQL

Upvotes: 1

Related Questions