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
What Is the Affect of Convert() on Index While Searching
What Is the Use of "&" Operator in SQL Server
There Are a Method to Paging Using Ansi SQL Only
Simple Recursive Query in Oracle
Mysql: Searching Between Dates Stored as Varchar
How to Return a New Identity Column Value from an SQLserver Select Statement
Update Statement in Oracle Using SQL or Pl/SQL to Update First Duplicate Row Only
Difference Between Datetime Converts in Msexcel and SQL Server
Why Do SQL Id Sequences Go Out of Sync (Specifically Using Postgres)
Is the Semicolon Necessary in SQL
Difference Between SQL Connection and Oledb Connection
Sql: Select Records Where All Joined Records Satisfy Some Condition
Entity Framework Indexing All Foreign Key Columns
How to Join Two Tables Together with Same Number of Rows by Their Order