CODEWITHSUNDEEP
mysql
Nikunj Chotaliya
Nikunj Chotaliya

Reputation: 802

Limiting number of results received based on a column value group without group by

Below is current output of my query: SELECT vans,icollaborate_id FROM mytable;

+==========+=====================+
|   vans   |   icollaborate_id   |
+==========+=====================+
| sachin   |   10                |
| cricket  |   10                |
| chess    |   10                |
| male     |   11                |
| 31-40    |   11                |
| sachin   |   11                |
| female   |   12                |
| tennis   |   12                |
| 21-30    |   12                |
+----------+---------------------+

Now i want to generate a result which returns the same output but with limit. For example if user specify limit=2 then output should be as below:

Expected output with limit 2

+==========+=====================+
|   vans   |   icollaborate_id   |
+==========+=====================+
| sachin   |   10                |
| cricket  |   10                |
| chess    |   10                |
| male     |   11                |
| 31-40    |   11                |
| sachin   |   11                |
+----------+---------------------+

Expected output with limit 3

+==========+=====================+
|   vans   |   icollaborate_id   |
+==========+=====================+
| sachin   |   10                |
| cricket  |   10                |
| chess    |   10                |
| male     |   11                |
| 31-40    |   11                |
| sachin   |   11                |
| female   |   12                |
| tennis   |   12                |
| 21-30    |   12                |
+----------+---------------------+

Please advise.

Upvotes: 1

Views: 78

Answers (4)

FlorinelChis
FlorinelChis

Reputation: 1478

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

My response below is invalid, thanks to Gordon Linoff for pointing that out.

I believe what you are looking for (if you want only 1 query) is this:

SELECT 
     vans, icollaborate_id 
FROM 
    mytable
WHERE icollaborate_id in
    (
        SELECT distinct t2.icollaborate_id FROM mytable t2 
               ORDER BY  t2.icollaborate_id LIMIT YOUR_LIMIT
    )

The subselect identifies how many distinct values should it look at then the main query selects all the rows that contain those values. This query can be quite ineficient, you may need to think this through.

Upvotes: 0

Ankur Raiyani
Ankur Raiyani

Reputation: 1708

Try This

SELECT vans,icollaborate_id FROM table_name WHERE icollaborate_id IN( SELECT * FROM (SELECT DISTINCT icollaborate_id FROM table_name LIMIT 3) as t);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271023

You can do this with a join:

SELECT vans, icollaborate_id 
FROM t join
     (select distinct icollaborate_id
      from t
      order by icollaborate_id
      limit 3
     ) tokeep
     on t.icollaborate_id = tokeep.icollaborate_id
order by icollaborate_id;

EDIT:

By the way, it is possible that the following query does what you want:

select icollaborate_id, group_concat(vans)
from t
group by icollaborate_id
limit 3;

This puts all the vans on a single row in a comma-delimited list.

Upvotes: 1

vishal shah
vishal shah

Reputation: 222

You can use DISTINCT query

SELECT DISTINCT icollaborate_id FROM table_name WHERE icollaborate_id='10';

Upvotes: 0

Related Questions