How do I select the first few rows when I have a clause where in mysql?

Question

I have this query for selecting the first 8 rows with a clause.. but if the clause is not valid return me next value of row 9 ,10 etc.. i want to check the first 8 rows and if 2 rows do not validate the clause return the only 6 rows .. this is the query i have:

$sql="SELECT * FROM nazir_items_ishop_category WHERE active = 1  order by id_cat asc limit 8";

It is possible to interrogate and receive an answer only from the first 8 rows ?

SERVER MYSQL VERSION: 5.6.41-cll-lve - MySQL Community Server (GPL) –

try reading this https://stackoverflow.com/questions/2249905/is-there-an-alternative-to-top-in-mysql there are several questions like this around already — Wolfaloo, Dec 04 '18 at 08:06
@Wolfaloo I already read there topic and you can see the order by and limit. But if i have the where clause and where clause is not valid , goes to the next row and I do not need that to go to row 9 or 10 etc. — Luțai Alexandru, Dec 04 '18 at 08:09

score 0 · Accepted Answer · edited Dec 04 '18 at 11:23

0

If you only want to look in the first 8 rows, you need to figure out their id_cat values, and then use IN with the subquery that finds them i.e.

SELECT *
FROM nazir_items_ishop_category
WHERE active = 1 
AND id_cat IN (SELECT id_cat 
               FROM nazir_items_ishop_category 
               ORDER BY id_cat ASC
               LIMIT 8)

If you can't use LIMIT in a subquery, you can use a JOIN to a derived table instead:

SELECT n1.*
FROM nazir_items_ishop_category n1
JOIN (SELECT id_cat,active 
      FROM nazir_items_ishop_category 
      ORDER BY id_cat ASC
      LIMIT 8) n2 ON n2.id_cat = n1.id_cat AND n2.active = 1

edited Dec 04 '18 at 11:23

Luțai Alexandru

13
6

answered Dec 04 '18 at 08:08

Nick

138,499
22
57
95

Wich version of MySQL support subquery?? i got this SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – Luțai Alexandru Dec 04 '18 at 08:12
@LuțaiAlexandru I've added an alternate solution using a `JOIN` instead. – Nick Dec 04 '18 at 08:15
Ok, I'll wait. I use this version of mysql server: 5.6.41-cll-lve - MySQL Community Server (GPL) – Luțai Alexandru Dec 04 '18 at 08:16
Yeah it's better but.. if i have in same table for example row 11 with active = 1 returns me the other one.. For example.. if i have first 8 rows with 3 active, and row 11 and 13 if have active =1 , "limit 8" return with row 11 and 13 – Luțai Alexandru Dec 04 '18 at 08:28
@LuțaiAlexandru OK I misunderstood your requirement. I've moved the test on `active` out of the subquery/derived table, so it should do what you want. – Nick Dec 04 '18 at 09:27
OMG , you're the best ! you missed to put active , on select n2. Works perfectly ! – Luțai Alexandru Dec 04 '18 at 09:49

How do I select the first few rows when I have a clause where in mysql?

1 Answers1