Search code examples
phpmysqlsqlsql-order-bysql-limit

PHP MYsql | Select the first 4 items that have a lower number

I need to select from this table, which has in the field: c_reg_num, the number of users who have assigned this product.

The first 4 products that have a lower number than the others

enter image description here

in the image you can see that the fields:

Campania

Emilia Romagna

Lazio

Marche

they have numbers other than 0, this means they have: 1,7,2,3 products assigned, I would like the query to bring out the first 4 fields that have numbers lower than those already existing.

How to tell which are the 4 products that have fewer assignments than the others?

name table: contatore_regioni

I would like the query to tell me: the top 4 products that have fewer assignments are: Abbruzzo, Basilicata, Calabria, Friuli venezia giulia (looking at the image) The question is simple count how many products have the most assignments and get me the first ones 4 who have less

test query:

<?php
$q_c_reg = $connessione->prepare("
SELECT MIN(c_reg_num) as c_reg_num, 
c_reg as c_reg
FROM contatore_regioni
group by c_reg
");
$q_c_reg->execute();
$r_cont_reg = $q_c_reg->get_result();?>
Solution

  • If you want the "first" rows where the product count is miminal, you can just order by and limit:

    select *
from contatore_regioni
order by c_reg_num, cont_reg limit 4

    This would return Abbruzzo, Basilicata, Calabria and Friuli Venezia Giulia for your sample data, in this particular order.