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.
3
votes
1answer
42 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
51 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
27 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
65 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
4k 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
134 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 ...
10
votes
1answer
8k 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 ...
7
votes
3answers
9k 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
499 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
4k 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 ...
3
votes
3answers
3k 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
95 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
91 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
191 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
848 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
699 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 / ...
