Stack Overflow
Stack Overflow

Questions tagged [sql-limit]

SQL clause to limit number of returned rows

The LIMIT clause is used to specify a maximum of rows to be returned in a SELECT query. The various SQL dialects use different syntax elements for that purpose. LIMIT x OFFSET y, is understood by MySQL, PostgreSQL, SQLite and some others RDBMS.
Example:

SELECT * FROM tbl LIMIT 10;

The SQL:2008 standard defines:

FETCH FIRST n ROWS ONLY

SQL Server uses TOP n, Oracle its rownum feature.
There is a comprehensive list on Wikipedia.

315 questions
2
votes
3 answers

Solution without LIMIT

I have a table like this (from LeetCode): requester_id accepter_id accept_date 1 2 2016/06/03 1 3 2016/06/08 2 3 2016/06/08 3 4 2016/06/09 Find the people who have the most friends and the most friends number. Expected…
Arpit
  • 333
  • 2
  • 9
2
votes
3 answers

How to get top value in Postgres when ties?

Everywhere people select top value using ORDER BY and LIMIT. But how to deal a scenario where the last element has duplicate entries. Consider a 5 row table name number_of_cookies matt 32 Greg 77 vance 21 chen 20 louise 77 Now I…
Xion
  • 319
  • 2
  • 11
2
votes
1 answer

Multiple Tables Search, with Limit Clause and Known Result Source Table Names

A situation arose where I have to heuristically find out a table name from a common id. The tables have the same exact structure so I thought union or union all will work as suggested by some answers here. The problem is, applying a limit clause to…
Ajowi
  • 449
  • 3
  • 12
2
votes
1 answer

SQL limit with spread

Is there a way to limit a selection like with LIMIT, but instead of returning the limitation with an offset, limit with a "spread". So for instance if a select returns 1000 rows and I limit it to 100, then I get every 10th row from start to end. I…
Jakob Simon-Gaarde
  • 675
  • 8
  • 26
2
votes
1 answer

Select multiple max values in mysql

Lets say i have two columns with diffrent values like this: id |val 1 | 9 7 | 6 7 | 8 I want to return the max id, and then find the max value according to the id. The row I return would be id: 7 and val: 8. How would I write this in…
Elias Knudsen
  • 315
  • 2
  • 9
2
votes
2 answers

Selecting the first N rows of each group ordered by date

I'm trying to list the first N rows (100 first) ordered by DateTime grouping like Master Detail. USE [Test] Create Table [dbo].[Masters] ( [MasterId] [nchar](36) NOT NULL PRIMARY KEY, [Tags] [nchar](100) NULL, [Numbers] [int] NOT…
user5113188
2
votes
4 answers

Return X elements before and after sorted by a property that is not unique SQL

I am trying to write a query that returns X elements before and after a given entity sorted by a property that is not unique. For example: Property a is the primary column (a unique UUID), b is the property I would like to sort by table …
jsbroks
  • 530
  • 6
  • 15
2
votes
2 answers

Repeatable Pseudorandom Sample of rows in PostgresSQL

I want to randomly sample some subset of my data, using a random seed so that it will be repeatable. Currently, I have this working minus the seed: select * from my_table where version is not null and start_datetime::date >= date('2020-03-16') …
Peter
  • 12,274
  • 9
  • 71
  • 86
2
votes
2 answers

MYSQL: return value associated with highest number with GROUP BY

In my table I have entries ordered by a timestamp. I want to return a single entry that has the highest timestamp. This works: SELECT max(`timestamp`) FROM `messages` However, it returns just the timestamp value. So I try to select the message…
lpetrucci
  • 1,285
  • 4
  • 22
  • 40
2
votes
1 answer

nth highest salary using subquery and dense_rank doesn't match when table has duplicate salaries

I was trying to get nth highest salary from table using subquery and dense_rank method. below are the results for each method. Subquery Method: select Top 1 Salary from ( select top 7 Salary from Employee order by Salary desc ) as Result order…
Kashinath Raut
  • 58
  • 6
2
votes
1 answer

MySQL LIMIT statement, wrong number of rows are returned

I have pretty complicated query (using MySQL 8.0.11) SELECT x.id, x.text, x.campaign_id, x.ad_group_id, x.close_variant, x.keywords, x.terms, x.impressions, x.clicks, x.conversion_value, x.avg_position, x.conversions, …
farincz
  • 4,943
  • 1
  • 28
  • 38
2
votes
1 answer

Why does adding ORDER BY drastically speed up query?

I've discovered some very strange and counter-intuitive behaviour in PostgreSQL. I have a query structure as follows. I am selecting both the IDs and the count from a subquery. The subquery does the filtering, joining, counting, but only orders by…
robbieperry22
  • 1,753
  • 1
  • 18
  • 49
2
votes
2 answers

MySQL Select & Limit computational complexity

Let's say I have a mysql table defined like this: create table test_table( id int(10) unsigned auto_increment primary key /*, other attributes...*/ ); And given that table I wanna fetch the last record from it like this: select * from test_table…
Razvan Tanase
  • 99
  • 1
  • 7
2
votes
2 answers

How to optimize MySQL "Order By Limit 1" in queries that join multiple tables?

So I have a query like this: SELECT tablea.name, tablea.views from tablea inner join tableb on (tablea.id = tableb.id and tablea.balance > 0) order by tablea.views asc limit 1 However, the problem is that when I run it, it runs quite slow (4+…
Oo Dee
  • 145
  • 1
  • 11
2
votes
1 answer

Average result of last 10 rows

I'm trying to find the average figure for the last 10 rows in a database table: select avg(Reading) from Readings Order By Rowid desc limit 10; This pulls the average of all entries in the table, not the last 10. I've tried all sorts of variations…
PeteBradshaw
  • 111
  • 1
  • 5
  • 11
1 2 3
…
20 21