OVER() vs Two Queries - Which is Most Efficient

Question

I need to pull back the first 300 rows from a 10MM row table, as well as getting a count of the total number of matching records.

I can do this in two queries, something like:

SELECT * FROM table WHERE field = value LIMIT 300;
SELECT count(*) FROM table WHERE field = value;

Or I could use an OVER():

SELECT *, COUNT(*) OVER() AS total FROM table WHERE field = value LIMIT 300;

Which would be the most efficient? I don't care about the need to run two queries, I'm after the most efficient solution. I'm no expert, and I've tried to run an "explain" but it doesn't make much sense to me. This is running on Amazon Redshift.

Answer 1

if your SortKey is timestamp field, the most efficient to run will be

select *
from(
select * , count(*) over() as total,
row_number () over(order by timestamp) as rank
from table
where filed =value)
where rank<301