MySQL Limit Select Statement

Question

Here an example Table:

+--------+------------+
| itemID | itemvalue  |
+--------+------------+
|      1 |          a |
|      2 |          b |
|      3 |          a |
|      4 |          a |
|      5 |          b |
|      6 |          a |
|      7 |          b |
|      8 |          a |
|      9 |          a |
|     10 |          b |
+--------+------------+

I like to know how many 'a' are in the first 5 rows of the table. So in words should do the following:

Select only the first 5 rows from table X WHERE itemvalue = a

This should return rows 1, 3 and 4

Is it possible to do this with just a Mysql Query and without further computing?

Do you mean the first 5 rows orderd by itemID?

wayneOS
– wayneOS

2021-07-26 08:43:00 +00:00
Commented Jul 26, 2021 at 8:43 — wayneOS
– wayneOS, Commented Jul 26, 2021 at 8:43

flyingfox · Accepted Answer · 2021-07-26 14:03:50Z

4

You can try with using an inner join query to do it

select t1.* from my_table t1
join
(select itemId from my_table order by itemId limit 5) t2
on t1.itemID=t2.itemID
where t1.itemvalue='a'

Working demo

edited Jul 26, 2021 at 14:03

answered Jul 26, 2021 at 8:44

flyingfox

13.5k4 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nevermind2001 Over a year ago

Thanks for your help but the result is: MySQL Error: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery. My MySQL Version is 8.0.25. But radocaws Soluition worked for my as well.

user16425306 · Accepted Answer · 2021-07-26 11:52:24Z

1

Here's one idea:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(itemID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,itemvalue CHAR(1) NOT NULL
);

INSERT INTO my_table VALUES
( 1,'a'),
(2,'b'),
(3,'a'),
(4,'a'),
(5,'b'),
(6,'a'),
(7,'b'),
(8,'a'),
(9,'a'),
(10,'b');

...

 WITH cte AS
 (
 SELECT *
      , ROW_NUMBER() OVER (ORDER BY itemid) x 
   FROM my_table
 )
 SELECT itemID,itemvalue FROM cte WHERE x <=5 AND itemvalue = 'a';
 +--------+-----------+
 | itemID | itemvalue |
 +--------+-----------+
 |      1 | a         |
 |      3 | a         |
 |      4 | a         |
 +--------+-----------+

answered Jul 26, 2021 at 11:52

user16425306

1 Comment

user16425306 Over a year ago

You're in good company, because neither do I! However, further insight can be gained by executing the 'cte' bit on its own.

Sandhya Srishti · Accepted Answer · 2021-07-26 08:45:37Z

-1

  SELECT * FROM table X WHERE itemvalue = a LIMIT $startingOffset, $count"

here in your case count is 5 and pass starting offset. if u pass 10 as starting offset it will fetch first 5 rows with starting index of 11

answered Jul 26, 2021 at 8:45

Sandhya Srishti

798 bronze badges

1 Comment

wayneOS Over a year ago

That would just give you all a rows until the limit is reached. The question was how to check if the first 5 rows have the value a.

Collectives™ on Stack Overflow

MySQL Limit Select Statement

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related