CODEWITHSUNDEEP
mysql
Nevermind2001
Nevermind2001

Reputation: 13

MySQL Limit Select Statement

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?

Upvotes: 0

Views: 78

Answers (3)

flyingfox
flyingfox

Reputation: 13506

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

Upvotes: 4

user16425306
user16425306

Reputation:

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         |
 +--------+-----------+

Upvotes: 1

Sandhya Srishti
Sandhya Srishti

Reputation: 79

  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

Upvotes: -1

Related Questions