-1

I am trying to run the below query but it is wrong as per MySQL syntax. I want to select the first 100 rows then want to apply group by to it.

select customerid from customer
limit 100
group by customerid

How can I achieve it?

forpas
  • 160,666
  • 10
  • 38
  • 76
Kakarot
  • 195
  • 2
  • 10
  • Please research your inquiry before posting in accordance with [ask]. Duplicate of [Limit SQL query result in MySQL](https://stackoverflow.com/questions/3399487/limit-sql-query-result-in-mysql) – esqew Mar 16 '22 at 15:42
  • 1
    Instead: `SELECT DISTINCT customerid FROM customer LIMIT 100;`. OR `SELECT customerid FROM (select customerid FROM customer LIMIT 100) GROUP BY customerid` to use the syntax you were aiming at. – JNevill Mar 16 '22 at 15:42
  • 1
    I don't see what purpose group by is serving here especially since there is no aggregation . Can you create sample data and expected output based on a lower limit? – P.Salmon Mar 16 '22 at 15:47
  • @P.Salmon this is a small part of a big query. – Kakarot Mar 16 '22 at 15:58
  • why we are not able to apply the limit before group by? – Kakarot Mar 16 '22 at 15:59
  • 2
    According to specs, limit is done after grouping. To force your desired behavior, use a sub query. – Salman A Mar 16 '22 at 16:13
  • A completely meaningless query. LIMIT without ORDER BY will return **some** 100 rows from all existing ones. – Akina Mar 16 '22 at 16:39
  • my question is not whether the query is meaningless or not question is different don't go to what query is, try to understand the problem if you can. – Kakarot Mar 16 '22 at 18:28
  • Are the customerid unique? you could improve this question by adding sample data for 10 rows and reducing limit to an appropriate level to simulate your requirement. – P.Salmon Mar 18 '22 at 10:40

1 Answers1

1

How about this? You need to add an aggregation column for it to work.

SELECT customerid
FROM   (SELECT *
        FROM   customer
        LIMIT  100) sub1
GROUP  BY sub1.customerid;
Darshit Parmar
  • 68
  • 7
  • it's not necessary to add an aggregation function, It will still work. – Kakarot Mar 17 '22 at 05:58
  • That's awesome! – Darshit Parmar Mar 17 '22 at 12:53
  • 'It will still work.' - only because mysql allows (other rdbms don') it so long as only_full_group_by is not set also without order by in subquery there is no guarantee that you will get the first 100 customerid and WHY would you need a group by are the customerid not unique? – P.Salmon Mar 18 '22 at 10:38
  • https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html – P.Salmon Mar 18 '22 at 10:40
  • 1
    customer-id isn't unique. @DarshitParmar answered has resolved my query. I have to use a subquery in order to use group by with limit. as the limit is done after grouping. Thanks – Kakarot Mar 18 '22 at 16:12
  • @Kakarot you can just mark my solution as accepted answer. – Darshit Parmar Mar 18 '22 at 22:12