Questions tagged [top]

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.

Filter by
Sorted by
Tagged with
1 vote
1 answer
514 views

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 ...
Traderhut Games's user avatar
0 votes
1 answer
153 views

Select Top item from group [duplicate]

I have a simple table ID Product Version Price Notes 1 Pizza 1 10.00 It is good 2 Pizza 2 11.00 The best yet 3 Soda 1 3:00 4 Hot dog 1 8.00 20% meat 5 Soda 2 3.00 Rebranded I want to get out ...
Davey's user avatar
  • 1
0 votes
2 answers
468 views

Strange behavior of TOP 1 clause sql server

I have a SQL Server database and restored it on another server on a different machine. Now I run following same query on both DBs which produces same below result set. select person_id, visit_id, ...
user1543848's user avatar
3 votes
1 answer
4k views

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 ...
EtherPaul's user avatar
  • 133
1 vote
1 answer
56 views

Next 10% of rows

I know TOP can return first 10% of row results, but how would I then get the next 10%? I am trying to break up a query that crashes due to insufficient memory (java.lang.OutOfMemoryError), and want to ...
KAE's user avatar
  • 143
0 votes
1 answer
428 views

How to show multiple record if has top 1

Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-...
Nathan's user avatar
  • 29
4 votes
1 answer
1k views

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/...
igelr's user avatar
  • 2,120
0 votes
1 answer
101 views

Top 10 each year

I need to write a query to show the number of tracks sold per artist per year for the top 10 best selling artists. Take the overall top 10 for all years. This is what I have: SELECT TOP 10 a.Name, ...
joachim soetinck's user avatar
1 vote
1 answer
35 views

Top N earliest punched in Employees across departments in a month

Find two of the earliest punched in Employees across departments in a month The dataset. Id EmpName EmpPunchInTime EmpDept 1 Dharma 26-JAN-2018 08:45 Engineering 2 ...
Lakshmana Gumpina's user avatar
1 vote
1 answer
862 views

Stored Procedure With an Optional Parameter Limiting Rows Returned if Specified

How would one go about having a sproc that allows a consumer to optionally specify the number of rows returned? If no row count is specified, then all rows returned.
ΩmegaMan's user avatar
  • 409
0 votes
2 answers
11k views

How to select TOP 1 value from each column where value is not null

I am trying to select many columns from a table where there can be multiple rows with the same ID. Each row contains data from columns that I want to return in one row. Any time a column has a value ...
Jordon Griffith's user avatar
4 votes
2 answers
173 views

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 ...
User1974's user avatar
  • 1,447
21 votes
1 answer
27k views

Why is "select *" faster than "select top 500 *" in SQL Server? [duplicate]

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 ...
user45867's user avatar
  • 1,651
17 votes
1 answer
7k views

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 ...
TiffanyP's user avatar
  • 171
9 votes
3 answers
29k views

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 ...
Neal's user avatar
  • 93
2 votes
1 answer
2k views

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 ...
SRag's user avatar
  • 21
4 votes
2 answers
14k views

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 ...
user avatar
2 votes
3 answers
11k views

How to select distinct TOP but group by column

I have a result set of 500 rows, but this are for 100 invoice. I need only the top 10 of the last invoices. I found something like similar: How to select top 10 records from each category Sample ...
Jan Van Looveren's user avatar
0 votes
1 answer
140 views

PostgreSQL, how to query the database to retrieve the percentile elements of a dataset?

I need a PostgreSQL query that do the following job: given a percentage (e.g. 10%), the query should return each 1st element of each percentile, among all the elements selected. So suppose in this ...
DavideChicco.it's user avatar
4 votes
2 answers
251 views

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 | ...
Alexandros's user avatar
  • 1,012
2 votes
2 answers
77 views

Cannot build correct SELECT in MySql

Please help me to create a correct MySql query, which I failed to do. I have 2 tables in our DB: CREATE TABLE IF NOT EXISTS `user_audit` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id`...
markovski's user avatar
4 votes
2 answers
412 views

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. ...
Greg R's user avatar
  • 43
0 votes
1 answer
1k views

Replacing a Cursor with a single Query possible?

I have this challenge where I'm trying to figure out if I can write something in a single query rather than using a cursor. The problem is I need to get items from a batch table based on a list of ...
Geoffrey Hall's user avatar
2 votes
2 answers
5k views

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 ...
Tiago's user avatar
  • 123
1 vote
2 answers
2k views

Postgres top-k with ties

Assume I have the following DB table with 3 integer fields. A | B | C 1 | 2 | 3 1 | 2 | 4 1 | 3 | 1 2 | 4 | 2 2 | 4 | 3 When I do: SELECT * FROM dbTable ORDER BY A,B LIMIT 1; I get 1 | 2 | 3 ...
Alexandros's user avatar
  • 1,012
3 votes
1 answer
3k views

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 ...
reticentKoala's user avatar
4 votes
2 answers
3k views

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 / ...
Kris Gruttemeyer's user avatar