The TOP keyword limits the number of query result rows returned to the client by the DBMS. Requires an associated ORDER BY clause for deterministic results.
Questions tagged [top]
29 questions
21
votes
1 answer
Why is "select *" faster than "select top 500 *" in SQL Server?
I have a view, complicated_view -- there are a few joins and where clauses. Now,
select * from complicated_view (9000 records)
is faster, much faster, than
select top 500 * from complicated_view
We're talking 19 seconds vs. 5+ minutes.
The first…
user45867
- 1,739
- 5
- 24
- 41
17
votes
2 answers
Unnecessary sort with TOP PERCENT?
Setup
-- Create a heap table of numbers from 1 to 100
SELECT TOP (100)
i = IDENTITY(int, 1, 1)
INTO #T
FROM master.dbo.spt_values;
-- Add a clustered primary key
ALTER TABLE #T
ADD PRIMARY KEY CLUSTERED (i);
Test query
Display 9% of the…
Paul White
- 94,921
- 30
- 437
- 687
17
votes
1 answer
SELECT TOP 1 from a very large table on an index column is very slow, but not with reverse order ("desc")
We have a large database, about 1TB, running SQL Server 2014 on a powerful server. Everything worked fine for a few years. About 2 weeks ago, we did a full maintenance, which included: Install all software updates; rebuild all indexes and compact…
TiffanyP
- 171
- 1
- 4
9
votes
3 answers
DISTINCT on one column and return TOP rows
How do you query for three unique customers with the largest Purchase_Cost?
I want to apply the DISTINCT only on Customer_Name, but the query below applies the distinct on all three columns. How should I modify the query to obtain the desired…
Neal
- 93
- 1
- 1
- 3
4
votes
2 answers
Pulling Top queries returns NULL in query plan and sql text
I'm using the following code to pull our top 20 queries (ordered by CPU):
SELECT TOP 20 qs.sql_handle
,qs.execution_count
,qs.total_worker_time AS [Total CPU]
,qs.total_worker_time / 1000000 AS [Total CPU in Seconds]
…
Kris Gruttemeyer
- 3,879
- 2
- 23
- 43
4
votes
1 answer
How to get tsql TOP PERCENT with OFFSET
select top 10 percent *
from sys.databases
order by database_id
I want to get the same 10 percent result using offset but this query works wrong
offset 0 rows
fetch next (select cast((count(*) * 10/100) as int ) from sys.databases) rows only
igelr
- 2,162
- 3
- 26
- 56
4
votes
2 answers
Access not properly translating TOP predicate to ODBC/Oracle SQL
I have a MS Access query that is based on a linked ODBC table (Oracle).
The DDL for the underlying Oracle table is:
create table road_insp
(
insp_id integer,
road_id integer,
insp_date date,
length number(10,2)
);
INSERT INTO…
User1974
- 1,517
- 25
- 54
4
votes
2 answers
Using TOP in INSERT INTO statement
I am reading a tutorial about TOP keyword in T-SQL. But I don't really understand the examples given in the tutorial.
Example 1:
INSERT TOP n INTO table_name1 (Col1, Col2)
SELECT Col3, Col4 FROM table_name2;
The tutorial mentions that the TOP here…
user99201
4
votes
2 answers
PostgreSQL Get top-k minimum per range
Assume I have the following data:
| f1 | f2 | f3 |
|----|----|----|
| 1 | 1 | 1 |
| 1 | 1 | 5 |
| 1 | 2 | 3 |
| 1 | 2 | 6 |
| 1 | 3 | 4 |
| 1 | 3 | 7 |
| 2 | 1 | 2 |
| 2 | 1 | 22 |
| 2 | 2 | 3 |
| 2 | 2 | 4 |
There are…
Alexandros
- 1,022
- 4
- 12
- 23
4
votes
2 answers
Execution plan has TOP operator for query without TOP or ORDER BY
I have product sales by month temporary table.(subset of much larger historical table) I'm running query to find new product not previously sold in the previous months for a specified 6 month range. So take January as base, in Feb show me all new…
Greg R
- 43
- 3
3
votes
1 answer
Using TOP and getting different result sets
I am trying to write an efficient query for deleting chunks of data. To this end I hoped to avoid an index scan by using the primary key to get the oldest records. However I'm seeing some unexpected results returned.
I hoped this
SELECT TOP 15…
reticentKoala
- 629
- 1
- 12
- 23
3
votes
1 answer
Select with CROSS APPLY runs slow
I am trying to optimize the query to run faster.
The query is the following:
SELECT grp_fk_obj_id, grp_name
FROM tbl_groups as g1
CROSS APPLY (SELECT TOP 1 grp_id as gid
FROM tbl_groups as g2
…
EtherPaul
- 133
- 1
- 5
2
votes
2 answers
Performance Order By large table with Join
Hello I have a table with 206,902 records and I am struggling to improve my query:
SELECT TOP (50) [ID]
,[Title]
,[Modified]
,[Created]
FROM [Data]
Inner Join [Permission]
On ([Data].[ID] = [Permission].[FichaID]
AND…
Tiago
- 123
- 1
- 6
2
votes
1 answer
Adding 'top 20' massively slows down query!
I have a query:
select * from Aview where field=20
order by id desc
This returns 2700 rows from the view in about 1 second.
Adding 'top 20' to the query makes MSSQL return in 43 seconds!!
This has been a HARD to reproduce issue, and doing a rebuild…
Traderhut Games
- 173
- 1
- 8
2
votes
1 answer
Using WITH TIES to retrieve multiple rows with same value
I am relatively new to SQLite. I have a database called "Clothes Catalog" that contains info on each data point's item type (shirts, pants, etc), catalog ID, and price.
I want to retrieve the maximum price of an item of each type, the item type,…
SRag
- 21
- 1
- 2