How to Limit the Results on a SQL Query

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> 

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

How to LIMIT the amount of data fetched in SQL

In databases that support LIMIT, the LIMIT applies to the result set not to the data being scanned. The SQL optimizer is free to choose whatever execution plan it wants. It can take the LIMIT into account, so the query is optimized for "time to first row" rather than "time to last row".

Next, you should be using proper, explicit, standard, readable JOIN syntax. If you want to limit the amount of data read, then you can put limit in a subquery:

SELECT a.column, b.column, c.column 
FROM (SELECT t1.*
      FROM t1 
      LIMIT 100
     ) a JOIN
     t2 b
     ON a.column = b.column JOIN
     t3 c
     ON c.column = b.column  -- or whatever;

Note: This is not guaranteed to return 100 rows, but it should limit the scanning of t1 -- which may or may not be relevant. Also, one of your original tags was BigQuery and merely limiting the number of rows scanned has no impact on performance (as opposed to pruning partitions).

I should also note that LIMIT is usually used with ORDER BY, so the result set is stable. That is, ORDER BY would make it consistent from one run to the next -- rather than returning an indeterminate (but not random) 100 rows.

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 SQL results to last 7 days

As you didn't mention your DBMS, the following is ANSI SQL:

select count(*) as cnt
from wlead
where f_id = '765'
  and date_created >= current_date - interval '7' day;

If date_created is actually a timestamp, you might want to compare that to a timestamp as well:

select count(*) as cnt
from wlead
where f_id = '765'
  and date_created >= current_timestamp - interval '7' day;

Note that this does not take care of "removing" the time part. current_timestamp - interval '7' day will yield a timestamp 7 days a ago at the same time (e.g. 20:52)

To "ignore" the time part you could cast date_created to a date:

select count(*) as cnt
from wlead
where f_id = '765'
  and cast(date_created as date) >= current_date - interval '7' day;

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   

Limiting SQL query to 10,000 results by where clause only, by string field

I think that I have figured this out. By using alphabetical sorting, we can still limit the query to ~11,000 results. We just have to respect that it starts sorting by the first digit/letter, and ignores the place value of the digits in the ID. So a number that is larger by magnitudes of 10, but starts with a lower digit, is treated as smaller:

By alphabetical sorting 9 > 100000

We can therefore use a where clause like this:

ID2 > '100000' and ID2 <= '101000'

That query will include all IDs from 100,000 to 101,000 and from 1,000,000 to 1,010,000. It would theoretically also include IDs from 10,000,000 to 10,100,000 and 100,000,000 to 101,000,000 etc. but I know that my values range from ~100,000 to ~4,000,000 (6 to 7 figures), so that range is irrelevant for this DB. I can then step my way through, all the way to:

ID2 > '999000' and ID2 <= '999999'

This will cover all values from 100,000 to 9,999,999 and let me get all data in 899 steps.

Thanks for all the other ideas provided!

---

Related Topics