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
3
votes
1answer
77 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/...
0
votes
1answer
57 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, ...
1
vote
1answer
29 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 ...
0
votes
1answer
80 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.
0
votes
2answers
5k 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 ...
4
votes
2answers
138 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 ...
12
votes
1answer
11k 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 ...
16
votes
1answer
2k 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 ...
8
votes
3answers
13k 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 ...
1
vote
1answer
605 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 ...
4
votes
2answers
5k 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 ...
2
votes
3answers
5k 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 ...
0
votes
1answer
101 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 ...
4
votes
2answers
103 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 | ...
2
votes
2answers
71 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`...
3
votes
2answers
196 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. ...
0
votes
1answer
900 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 ...
2
votes
2answers
4k 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 ...
1
vote
2answers
775 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 ...
2
votes
1answer
2k 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 ...
4
votes
2answers
2k 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 / ...