Sql Query - Limiting Query Results

How to limit the results on a SQL query

Yes, this is possible. This differs between db engines.

Postgres:

SELECT * FROM <table> LIMIT 50

SQL Server:

SELECT TOP 50 * FROM <table> 

Sql Query - Limiting query results

Using two user variable and counting the same consecutive store_id, you can replace <= 5 with whatever limit you want

SELECT a.*
FROM (
 SELECT store_id, user_id, count(1) as visits 
 FROM shopping
 WHERE store_id IN (60,61,62,63,64,65,66)
 GROUP BY store_id, user_id
 ORDER BY store_id, visits desc, user_id
) a,
(SELECT @prev:=-1, @count:=1) b
WHERE
 CASE WHEN @prev<>a.store_id THEN
   CASE WHEN @prev:=a.store_id THEN
    @count:=1
   END
 ELSE
   @count:=@count+1
 END <= 5

Edit as requested some explanation :

The first subquery (a) is the one that group and order the data so you will have data like:

store_id | user_id | visits
---------+---------+-------
 60           1       5
 60           2       3
 60           3       1
 61           2       4
 61           3       2

the second subquery (b) init the user variable @prev with -1 and @count with 1

then we choose all data from the subquery (a) verifying the condition in the case.

• verify that the previous store_id (@prev) we have seen is different from the current store_id.
  Since the first @prev is equal to -1 there is nothing that match the current store_id so the condition <> is true we enter then is the second case who just serve to change the value @prev with the current store_id. This is the trick so i can change the two user variable @count and @prev in the same condition.

• if the previous store_id is equal to @prev just increment the @count variable.

• we check that the count is within the value we want so the <= 5

So with our test data the:

step | @prev | @count | store_id | user_id | visits
-----+-------+--------+----------+---------+-------
  0      -1      1    
  1      60      1        60          1        5 
  2      60      2        60          2        3
  3      60      3        60          3        1
  4      61      1        61          2        4
  5      61      2        61          3        2   

How to Limit Query Result to Organizations with 2 or More Accounts Associated with it

You need one more join or subquery in where clause which will give you organizations with at least 2 or more accounts. Below is the join version.

FROM organizations p
INNER JOIN organizations o ON o.parent_org_id = p.id
INNER JOIN (Select a.organization_id,Count(distinct a.id) as num_of_accounts
            FROM accounts a 
            GROUP BY a.organization_id
            HAVING Count(distinct a.id) >= 2) accnts ON accnts.organization_id = o.id

Limiting SQL Query to Just a Few Results

Sure!
T-SQL

Select TOP 10 id, first_name, last_name

from customers
Where country = 'US'

For anything ANSI Compliant see this question which has a similar aim:
ANSI SQL version of SELECT TOP 1

Is there a way to limit a query based on the result of another in mySQL?

You can make it a variable and use it in the second piece of code:

SET @number_required = (SELECT ROUND(0.07 * COUNT(*), 0) AS number_required
                        FROM   trinkets t
                        LEFT JOIN orders o ON p.order_id = o.id
                        WHERE o.trinket_id IN ( 27 )
                        AND t.valid_from >= '2022-01-01'
                        AND t.valid_from <= '2022-01-31')

Note: Call it as @number_required not number_required

Limit query result once value has been reached

One method uses window functions:

select t.*
from (select t.*,
             min(case when high > target then date end) over (partition by ticker) as first_target_date
      from t
     ) t
where date >= first_target_date or
      first_target_date is null;

SELECT LIMIT 1 query returns unexpected results when the queried table field has an index

In practice, current versions of InnoDB reads rows in order by the index it uses to access the table. If no index is used, it reads the rows in order by the clustered index (that is, the primary key order).

Demo: I create a table that has a clustered index (id) and another indexed column (x), and another non-indexed column (y).

mysql> create table test (id serial primary key, x int, y int, key(x));

I fill 3 rows, such that the order of the primary key is opposite the order of the other rows:

mysql> insert into test values (1, 6, 13), (2, 5, 12), (3, 4, 11);

If I query all the columns without specifying an order, they are returned in primary key order:

mysql> select * from test;
+----+------+------+
| id | x    | y    |
+----+------+------+
|  1 |    6 |   13 |
|  2 |    5 |   12 |
|  3 |    4 |   11 |
+----+------+------+

If I query only the unindexed column y, they are also returned in primary key order:

mysql> select y from test;
+------+
| y    |
+------+
|   13 |
|   12 |
|   11 |
+------+

If I query only the indexed column x, the rows are read from the index on x, and are returned in that order:

mysql> select x from test;
+------+
| x    |
+------+
|    4 |
|    5 |
|    6 |
+------+

If I query the indexed column x with the primary key, the rows are still returned in the order of x:

mysql> select id, x from test;
+----+------+
| id | x    |
+----+------+
|  3 |    4 |
|  2 |    5 |
|  1 |    6 |
+----+------+

Why is that? EXPLAIN can show us:

mysql> explain select id, x from test\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: index
possible_keys: NULL
          key: x
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index

The key field of the EXPLAIN shows us that it's reading from the index x. InnoDB indexes always contain the primary key value, so in this case there's no need for MySQL to read anything but the index.

All these internal rules can be difficult to remember. It's a much better idea to use an ORDER BY clause to be explicit if you need the rows to be returned in a particular order. The query engine may still read the rows in whatever order the storage engine chooses, but it will re-sort them if necessary before returning the result set.

---

Related Topics