SQL SELECT TOP | LIMIT | ROWNUM Clause

The SQL SELECT TOP | LIMIT | ROWNUM clause is used to retrieves TOP N number or X percent records from query result.

The SQL SELECT TOP clause is used to fetch or extract specify the number of records returned based on a fixed value or percentage.

The SQL SELECT TOP clause is mainly used to make performance while handling large number of records from query result.

Note: All the databases do not support TOP clause.

For Example:

• SQL "TOP" clause will work only on Microsoft Sql Server and Microsoft Access
• SQL "LIMIT" clause will work only on MySql
• SQL "ROWNUM" clause will work only on Oracle

INSERT INTO Table1
SELECT TOP 100 *
FROM Table2
WHERE EXISTS (SELECT field1, field2, count(*)
from Table2
group by field1, field2
having count(*) > 10)

As a result, field1 must have a count greater than 10, and field2 must have a count greater than 10. I'm always receiving records with a count of fewer than 10.

ORDER BY clause with desc (descending) attribute and Limit 1 can be used to choose the last row.

Syntax:

SELECT column_Name FROM Table_Name ORDER BY Column_Name DESC LIMIT 1;  

We must supply the value 1 directly after the LIMIT keyword in this MySQL syntax to indicate a single row/record.

Example 1: Get the last 5 records from the table sales_team_emails

We need to get 'N' records (in this case, five). For the answer, look at the query below.

SELECT *
FROM
sales_team_emails
ORDER BY sales_person_id DESC
LIMIT 5;

Example 2: Example of LIMIT Clause in MySQL:

To demonstrate how to use the LIMIT clause in MySQL to obtain the last record, consider the Employee table:

The last value of the Emp_City column in the above Employee table is seen in the MySQL query below:

SELECT Emp_City FROM Employee ORDER BY Emp_City DESC LIMIT 1;  

Output:

Example 3: Our lats record is with id 4 and Name ‘Carol’. To get the last record, the following is the query.

select *from getLastRecord ORDER BY id DESC LIMIT 1;

Output:

The above output shows that we have fetched the last record, with Id 4 and Name Carol.

Unless the query produces fewer records than the number provided by LIMIT, LIMIT and OFFSET are used to get only a few entries from the output of a query.

The LIMIT and OFFSET clauses are used to limit the number of rows returned by a select query.

indicate an OFFSET from which data should be returned.

The OFFSET function is used to remove a specified number of records from the results.

Syntax:

The following shows the syntax of LIMIT & OFFSET clauses:

SELECT 
  column_list
FROM
  table1
ORDER BY column_list
LIMIT row_count OFFSET offset;

Example 1: The following example uses both LIMIT & OFFSET clauses to return five rows starting from the 4th row:

SELECT 
  employee_id, first_name, last_name
FROM
  employees
ORDER BY first_name
LIMIT 5 OFFSET 3;

Example 2: specify an OFFSET from where to start returning data.

SELECT * FROM artists LIMIT 5 OFFSET [Number of rows to skip];

Say you want to get 5 artists, but not the first five. You want to get rows 3 through 8. You’ll want to add an OFFSET of 2 to skip the first two rows:

SELECT * FROM artists LIMIT 5 OFFSET 2;

Example 3: When using LIMIT, it's critical to include an ORDER BY clause to force the rows to appear in a specified order. Otherwise, a random subset of the query's rows will be returned. Unless you provide ORDER BY, the ordering is uncertain.

limit_offset_demo=# select count(*) from public."Album";

Output:

limit_offset_demo=# select * from public."Album" limit 4;

Example 4: The OFFSET keyword is used after the first argument in the LIMIT clause with two arguments, as seen below:

SELECT * FROM users ORDER BY id ASC LIMIT 2 OFFSET 3

Output:

Notice that in this alternative syntax the arguments have their positions switched: the first argument represents the number of rows to be returned in the result set; the second argument represents the offset.

MySQL Limit can return a range of rows from a database table, return the first so many rows from a database table, or give the first so many rows from a database table.

The number of rows returned by a SQL query can be limited.

For instance, if a user knows exactly which rows of a table they want, they can use MySQL limit syntax to indicate which of those rows to return. When querying really large tables, this is especially beneficial.

Example 1: Returning a range of rows from the employee table (starting 2 rows past the first record, return the next 4 rows). In this case, the OFFSET is 2 and the number of rows to return is 4:

SELECT	*
FROM
employee
LIMIT 2,4 

Example 2: If you're looking for a result that falls between two value ranges. For instance, if you need a big number of rows to display data on an application, divide it into pages with a limit of 10 rows per page. In that instance, the following query will return a range of rows:

SELECT emp_id, emp_name, income FROM employees  
ORDER BY income DESC  
LIMIT 3,7;

The SELECT TOP command is always used in conjunction with the ORDER BY clause since the order of entries stored in a table is unspecified. As a result, the result set is restricted to the first N-sorted rows.

You can utilize SQL's TOP clause in your SELECT statement to manage such circumstances. However, only SQL Server and MS Access database systems allow the TOP clause.

The LIMIT clause in MySQL is analogous to the ROWNUM clause in Oracle, which limits the number of rows returned by a query.

The SQL SELECT TOP statement is used to extract a specific number of records, rows, or percentages from one or more database tables, and to limit the number of records returned based on a defined value or percentage.

In the result, it shows the top N number of rows from the tables. This clause is used when the database tables contain thousands of records.

Get the top 10 new employees, or the top 3 pupils based on their grades, or anything similar.

Syntax:

The syntax for the SELECT TOP statement in SQL is:

SELECT TOP (top_value) [ PERCENT ]
expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

The number in the syntax represents the number of rows in the output that are shown from the top. The column whose record we wish to show in the output is denoted by column_Name. The WHERE clause can also be used to indicate the criteria.

Example 1: Suppose we've an employees table in our database with the following records:

The following statement returns top three highest-paid employees from the employees table.

SELECT TOP 3 * FROM employees
ORDER BY salary DESC;

Output:

You can optionally use the PERCENT keyword after the fixed value in a TOP clause, if you just want to retrieve the percentage of rows instead of fixed number of rows. Fractional values are rounded up to the next integer value (e.g. 1.5 rounded to 2).

Example 2: The following example uses a constant value to return the top 10 most expensive products.

SELECT TOP 10
 product_name, 
 list_price
FROM
 production.products
ORDER BY 
 list_price DESC;

Example 3: The following examples will help you how to use the Number and Percent in SQL TOP clause in the query:

If we have a table called Cars with three columns:

Assume you wish to display the first three names and car colours from the table above. You must type the following SQL query to accomplish this:

SELECT TOP 3 Car_Name, Car_Color FROM Cars;  

Output:

Example 4: Let's look at a SQL example, where we use the TOP PERCENT keyword in the SELECT statement.

SELECT TOP(10) PERCENT
contact_id, last_name, first_name
FROM contacts
WHERE last_name = 'Ramani'
ORDER BY contact_id;

The first 10% of the records from the entire result set would be selected using SQL SELECT TOP. The SELECT command in this case would return the top 10% of records from the contacts table where the last name is 'Ramani'. The SELECT statement would not give the remaining 90% of the result set.

Select the TOP (1) value (DASHBOARD_STATUS_LEVEL) for each group (by MARKET_PROJECT) and place in an alias column (PROJECT_LEVEL).

Example 1: The SELECT statement below is a subquery that I've attempted that seems to just copy the DASHBOARD_STATUS_LEVEL into the PROJECT_LEVEL:

(SELECT TOP (1)SQL10.DASHBOARD_STATUS_LEVEL
FROM dbo.OVP_CLAIM
GROUP BY MARKET_PROJECT
ORDER BY SQL10.DASHBOARD_STATUS_LEVEL DESC)
AS PROJECT_LEVEL

Example 2:

SELECT A.*
FROM dbo.Customers AS C
  CROSS APPLY (SELECT TOP (1) custid, orderdate, orderid, filler
  FROM dbo.Orders AS O
  WHERE O.custid = C.custid
  ORDER BY orderdate DESC, orderid DESC) AS A;

In the low-density situation (upper plan), the optimizer scans the clustered index of the Orders database for each partition, builds an index (index spool), and performs a Top N Sort. The optimizer does not generate an index in the high-density scenario (lower plan), but it does follow the other phases. Both proposals call for a large number of reads (more than 3,000,000 in the low-density scenario and more than 300,000 in the high-density case), as well as sorting expenses (albeit it's not a full sort, but rather a Top N Sort for filtering).

TOP keyword SELECT TOP 1 salary FROM (SELECT TOP 3 salary FROM Table_Name ORDER BY salary DESC) AS Comp ORDER BY salary ASC.

Example 1: Find the Third Highest Salary Using a Sub-Query :

SELECT TOP 1 SALARY.
FROM (
SELECT DISTINCT TOP 3 SALARY.
FROM tbl_Employees.
ORDER BY SALARY DESC.
) RESULT.
ORDER BY SALARY.

Example 2:

SELECT *
FROM table
WHERE 
(
  sal IN 
  (
   SELECT TOP (3) sal
   FROM table as table1
   GROUP BY sal
   ORDER BY sal DESC
  )
)

TOP TEN QUESTIONS, We can retrieve records in the desired ascending order of the ID column if we use ORDER BY ID ASC. However, the need is to extract the BOTTOM 5 records in ascending order by ID column. There is no BOTTOM 10" clause, unlike the TOP 10.

In SQL Server, the TOP clause is used to limit the number of rows returned as a percentage. We must also organise the rows in descending order to select the records from the last.

Example 1: The following script can be used to complete the task and obtain the desired result.

Return bottom 10

SELECT TOP 10 SaleID, SaleValue FROM Sales ORDER BY SaleValue ASC

This work is usually implemented when a table contains thousands of records. However, to make things easier, we'll use an example table with only 20 records. We'll try to pick the top 10 records from it.

Example 2: Usually ORDER BY clause is not allowed in CTE’s; Except when TOP clause is used.

Use CTE to pick the records we want

; WITH Bottom5 (ID, Name)
AS
    (
        SELECT TOP 5 ID, Name
        FROM #OrderTest
        ORDER BY ID DESC
    )
SELECT *
FROM Bottom5
ORDER BY ID ASC
GO

Let us increase the number of records returned from the previous queries to be 100 records:

SELECT TOP 100 [ProductID]
  ,[Name]
  ,[ProductNumber]
FROM [MSSQLTipsDemo].[Production].[Product]
ORDER BY ProductID 
OPTION (RECOMPILE)
GO
 
SELECT PP.[ProductRank],PP.ProductID , PP.[Name]
      ,PP.[ProductNumber]
FROM 
(
   SELECT ROW_NUMBER() OVER (ORDER BY [ProductID]) AS [ProductRank] 
      ,ProductID
      ,[Name]
      ,[ProductNumber]
   FROM [MSSQLTipsDemo].[Production].[Product]
   ) AS PP
WHERE PP.[ProductRank] <=100
ORDER BY [ProductRank] 
OPTION (RECOMPILE)

Using a union is the only thing I can think of to accomplish this

select * from (select top(5) * from logins order by USERNAME ASC) a
union
select * from (select top(5) * from logins order by USERNAME DESC) b

Example 1: To specify multiple CASE conditions for the TOP Clause.

SELECT TOP 5 Nationalidnumber ,
CASE salariedflag
WHEN 1 THEN 'Active Employee'
WHEN 0 THEN 'Inactive Employee'
ELSE 'Invalid Value'
END AS [Salaried Flag]
FROM [AdventureWorks2019].[HumanResources].[employee]

Example 2: corrrect the case and top keyword

select (select top 1 case STATUS 
  when 'Inprocess' then 'Processing' 
  when 'Inworkbin' then 'Waiting In Draft' 
  end 
 from ICS_EMAIL_CONNECTIONS_TRX A    
  where A.SESSIONID = B.SESSIONID 
  and STATUS <> 'Completed'
  order by A.CREATE_DATE desc) as LAST_STATUS 

Example 1:

SQL SERVER:

SELECT TOP 3 COUNTRY, count(*) 
FROM DRUG_SEIZURE 
WHERE COUNTRY IS NOT NULL 
GROUP BY COUNTRY
ORDER BY count(*) DESC

MySQL:

SELECT COUNTRY, count(*) 
FROM DRUG_SEIZURE 
WHERE COUNTRY IS NOT NULL 
GROUP BY COUNTRY
ORDER BY count(*) DESC
LIMIT 3

Oracle:

SELECT *
FROM (
  SELECT COUNTRY, count(*) 
  FROM DRUG_SEIZURE 
  WHERE COUNTRY IS NOT NULL 
  GROUP BY COUNTRY
  ORDER BY count(*) DESC
) mr
WHERE rownum <= 3
ORDER BY rownum;

Example 2:

SELECT TOP 3 WITH TIES [MLBTeam], COUNT(*) AS [WinCount]
FROM [dbo].[WorldSeries]
GROUP BY [MLBTeam]
ORDER BY COUNT(*) DESC
GO

Output:

The DELETE TOP command in SQL Server is used to delete records from a table while also limiting the number of records destroyed by a set value or percentage.

The delete statement can also be used as a Delete TOP, allowing you to utilise TOP(n) with the delete statement to delete the first n rows from the table, and TOP (n) PERCENT to delete the top rows based on a n percentage of the overall result set.

Syntax:

DELETE TOP (top_value) [ PERCENT ] 
FROM table
[WHERE conditions];

TOP (top_value) - It deletes the top number of rows in the result set based on top_value.

Example 1: Delete TOP 25% rows from the table where salary is greater than 20000.

DELETE TOP(25) PERCENT
FROM [w3cschoool].[dbo].[Employees]
WHERE salary > 20000; 

Delete top 2 rows from the table "Employees" where salary is greater than or equal to 20000.

DELETE TOP(2)  
FROM [javatpoint].[dbo].[Employees]  
WHERE salary >= 20000; 

Example 2: Let's look at a SQL Server example, where we use the TOP PERCENT keyword in the DELETE statement.

DELETE TOP(25) PERCENT
FROM employees
WHERE first_name = 'Selin';

The first 25% of the records matching the DELETE TOP criterion would be deleted in this SQL Server DELETE TOP example. In this case, the DELETE TOP statement would remove the top 25% of records from the employees table with the first name 'Selin'. The DELETE TOP statement would not delete the remaining 75% of the records that met this condition.

Example 3: For example, TOP(5) would delete the top 5 rows matching the delete criteria.

Lets delete top 5 records from table those are having amount value greater than 500.

Before deleting records lets see how many records are there in table which have amount values greater than 500.

You can see, there are 8 records for which amount values are greater than 500.

Lets delete top 5 records those are having amount values are greater than 500.

DELETE TOP(5) FROM TBL
WHERE Amount >500

In SQL Server, the DELETE TOP command deletes entries from a table while also limiting the number of records depending on a current value or percentage.

Syntax:

DELETE TOP command syntax

DELETE  TOP (giatri_dau) [PERCENT] 
FROM bang 
[WHERE dieu _kien]; 

Because the DELETE statement deletes all entries in the table, there is no need to list all fields.

Example 1: deleting based on a percentage using TOP PERCENT.

For example, to delete 50% of records, we can do the following:

DELETE TOP (50) PERCENT FROM customer;

Example 2: We can use TOP with ORDER BY in a subselect statement like this to delete a set of ordered rows:

DELETE T FROM (
  SELECT TOP (5) id
  FROM customer
  ORDER BY name
) T;

Or, alternatively:

DELETE FROM customer
WHERE id IN
(
    SELECT TOP (5) id
    FROM customer
    ORDER BY name
);

Output:

Because the rows referenced in the TOP expression are not sorted in any order, you can't just use DELETE TOP to delete ordered rows.

When utilising a non-key column in a subselect statement, be mindful that it may result in the deletion of more rows than planned. This could be due to the fact that the result contains duplicate values.

Example 3: use the keyword TOP

DELETE TOP(10) 
 FROM nhanvien 
  WHERE ho = 'Andrew'; 

The DELETE TOP command, in this case, will delete the first ten records in the table belonging to the Andrew employee family. There will be no deletion of additional records with the surname Andrew.

In a column, the functions DISTINCT and TOP are used. It can't be used on many columns, either.

Example 1: The DISTINCT is only applied to Customer_Name in the query following, but it is applied to all three columns.

SELECT DISTINCT TOP 3 customer_name, order_no, Purchase_Cost  
FROM PurchaseTable 
ORDER BY Purchase_Cost

Example 2: TOP 10 returns the first ten items from the sorted list, whereas DISTINCT eliminates duplicates. Which comes first, the question?

SELECT   DISTINCT TOP 10 FirstName,LastName
FROM     Person.Person
ORDER BY LastName; 

Please remember that DISTINCT works on all columns and expressions in the SELECT clause before answering this question. As a result, the statement will return separate rows for FirstName and LastName in this example.

Unfortunately, using DISTINCT on one set of fields while displaying others is not possible. When you include columns in a SELECT statement, they are affected by the DISTINCT operator. I say direct because you could get a separate list and then use an INNER JOIN to add other columns. However, there are risks in doing so, as the join may generate duplicates.

Adding a TOP clause to DISTINCT :

Select DISTINCT Values from Table and order

Select the TOP x rows from the results in step 1 and display

Example 3:

SELECT distinct TOP 10 MyId FROM sometable

is functionally identical to select top 10 p.id from(select distinct p.id from tablename)tablename

Step 1:

create table #names ([name] varchar(10))

Step 2: Insert records

insert into #names ([name]) values ('jim')
insert into #names ([name]) values ('jim')
insert into #names ([name]) values ('bob')
insert into #names ([name]) values ('mary')
insert into #names ([name]) values ('bob')
insert into #names ([name]) values ('mary')
insert into #names ([name]) values ('john')
insert into #names ([name]) values ('mark')
insert into #names ([name]) values ('matthew')
insert into #names ([name]) values ('luke')
insert into #names ([name]) values ('peter')

Step 3:

select distinct top 5 [name] from #names

select top 5 * from (select distinct [name] from #names) subquery 

drop table #names

Output:

Select top 5 distinct isn't valid, but pick distinct top 5 is, and it behaves as you'd expect select top 5 distinct to work.

In the OFFSET FETCH portion, add the where clause to the query as well.

Example 1: If the results are sorted, use OFFSET and FETCH to reproduce the rows returned from TOP. The following statement returns the TOP 10 results:

SELECT Name,
   ProductNumber,
   StandardCost
FROM Production.Product
ORDER BY StandardCost
   OFFSET 0 ROWS
   FETCH NEXT 10 ROWS ONLY

Example 2: The initial 20 rows are followed by the second 20 rows, the third 20 rows, and so on. Websites that present information one page at a time commonly utilise this style of query. The first 20 rows are quite simple. That is a top-notch operation. When you need a second or third group of 20, things get a lot more complicated.

DECLARE @Offset INT = 100;
DECLARE @Fetch INT = 20;
 
SELECT EmailAddress
FROM
    (SELECT TOP (@Fetch) EmailAddress
    FROM
        (SELECT TOP (@Offset + @Fetch) EmailAddress
        FROM Sales.vIndividualCustomer
        ORDER BY EmailAddress) x
    ORDER BY EmailAddress DESC) y
ORDER BY EmailAddress;
GO

Example 3: These two queries could return different results:

SELECT *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id
OFFSET 0 ROWS
FETCH NEXT (SELECT CAST(CEILING((COUNT(*) * 10.0/100.0)) as int) FROM sys.databases) ROWS ONLY;

SELECT top 10 percent *
FROM sys.databases
where name not like '%A%'
ORDER BY database_id;

Tables are Data is not put in any particular order by default. You can use the orderby clause to insert items based on the user's preferences.

Another SQL clause that we advocate using with SELECT TOP is the ORDER BY clause, which sorts your rows by particular columns.

To limit the amount of rows or the percentage of rows returned by a query, use the SELECT TOP SQL statement.

Example 1: The TOP clause is always used with the SELECT query in SQL. When using the SELECT query to select records, but only needing the first three, use the TOP clause to return only the first 3 rows.

SELECT TOP 3 * 
  FROM employee_hour 
  ORDER BY hours DESC;

Output:

Example 2: You must sort the data using the ORDER BY statement if you want to find the first three clients with the highest annual income. Then, using this Top Clause, you must obtain the first three consumers.

SELECT TOP 5 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY YearlyIncome DESC

Data will be sorted by the [Yearly Income] in the descending order, then it will retrieve Top five rows.

Example 3: Select the first 10 employees hired most recently.

SELECT TOP(10)JobTitle, HireDate  
FROM HumanResources.Employee  
ORDER BY HireDate DESC;  

ORDER BY clause is used to return the top 10 recently hired employees.

Example 4: Lets take below examples

SELECT TOP 10
  [Customer ID], [Customer Name], [Province], [region]  
FROM
  [Customers_Tbl]
ORDER BY 
  [Customer ID] DESC;

Above example extract the TOP 10 mentioned columns sorted by [Customer ID] in descending order.

Example 1: get the top 2 records for each category from table CategoryMaster.

;With CTE
AS
(
   SELECT ID, Category, 
   SubCategory,
   Amount,
   ROW_NUMBER() OVER (PARTITION BY Category Order by Amount DESC) AS Sno# 
   FROM dbo.CategoryMaster
) 
SELECT * FROM CTE WHERE Sno# <=2

Example 2: You can do this bit more compactly in SQL Server 2008 as follows:

select top (1) with ties
  C.CompanyName,
  Year(O.OrderDate) as Yr,
  sum(OD.Quantity) as Total
from Orders as O
join Customers as C on C.CustomerID = O.CustomerID
join "Order Details" as OD on OD.OrderID = O.OrderID
group by C.CompanyName, Year(O.OrderDate)
order by 
  row_number() over (
    partition by Year(O.OrderDate)
    order by sum(OD.Quantity) desc
  );

Example 3: To return the first 10 records from the Product table in two ways, the first using the TOP statement and the second using the ROW_NUMBER statement. I enabled the TIME and IO statistics and the Actual Execution Plan to compare the queries performance:

SELECT TOP 10 [ProductID]
      ,[Name]
      ,[ProductNumber]
FROM [MSSQLTipsDemo].[Production].[Product]
ORDER BY ProductID 
OPTION (RECOMPILE)
GO
 
SELECT PP.[ProductRank],PP.ProductID , PP.[Name]
      ,PP.[ProductNumber]
FROM 
(
   SELECT ROW_NUMBER() OVER (ORDER BY [ProductID]) AS [ProductRank] 
      ,ProductID
      ,[Name]
      ,[ProductNumber]
   FROM [MSSQLTipsDemo].[Production].[Product]
   ) AS PP
WHERE PP.[ProductRank] <=10
ORDER BY [ProductRank] 
OPTION (RECOMPILE)

In the SELECT statement, use the TOP PERCENT term. It's a choice. The top rows are dependent on a percentage of the overall result set (as provided by the top_value) if PERCENT is defined.

For example, TOP(10) PERCENT would return the top 10% of the full result set.

Example 1: We may build a query like this to identify the Top 5% of Employees having available vacation hours:

SELECT TOP 5 PERCENT NationalIDNumber,JobTitle, VacationHours
FROM HumanResources.Employee
ORDER BY VacationHours Desc

Example 2: Uses PERCENT to specify the number of employees that are returned in the query result set. There are 290 employees in the HumanResources.Employee table. Because five percent of 290 is a fractional value, the value is rounded up to the next whole number.

SELECT TOP(5)PERCENT JobTitle, HireDate  
FROM HumanResources.Employee  
ORDER BY HireDate DESC;  

Example 3: To indicate the number of consumers the query must return, we utilise SQL TOP PERCENT. We're going to use the first 40%. Because our customer's table has 15 rows, it means 6 rows.

SELECT TOP 40 PERCENT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]

Example 4:

SELECT TOP(10) PERCENT
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

The first 10% of the records from the entire result set would be selected in this SQL Server SELECT TOP instance. The SELECT statement in this case would return the top 10% of records from the employees table where the last name is 'Anderson.' The SELECT statement would not return the remaining 90% of the result set.

To retrieve random records, use NewID () in Order by.

Example 1: If we want to get 3 random records from the dbo.Customer table, we may use the query below.

Select top 3 * From dbo.Customer
order by NEWID()

add more rows to the table to see the desired result.

Here is the query to get rows in rondom order.

SELECT TOP 10 * FROM dbo.Books ORDER BY NEWID()

Example 2: order the table by a random number and obtain the first 5,000 rows using TOP.

SELECT TOP 5000 * FROM [Table] ORDER BY newid();

Something like in LINQ skip 5 and take 10?

SELECT TOP(10) * FROM MY_TABLE  
WHERE ID not in (SELECT TOP(5) ID From My_TABLE ORDER BY ID)
ORDER BY ID;

You'll need to establish some sort of order (by Id, for example) so that all of the rows are shown in a consistent fashion.

In a UPDATE statement, the TOP Statement is used to limit the number of rows that are updated. When you use the TOP (n) clause with UPDATE, the update action is executed on a random selection of 'n' rows.

Example 1: You must use TOP in conjunction with ORDER BY in a subselect statement to apply updates in a relevant chronology.

UPDATE top (3)  EmployeeDetail set EmpAddress = 1   

The following example updates the top 3 random rows in the EmployeDetails table.

Example 2: I'd LIKE to use an "ORDER BY" in an update.

update top (1) [dbo].[MyTable] set [MyField] = 'xyz'
where [Field2] = 0 and [Field3] = 1
ORDER BY [MyTable].[ID]

of course, the ORDER BY does not work in an UPDATE.

Does anyone know how SQL will select TOP (1) in this query:

Example 3: UPDATE and TOP combined? I'd like to change the top 1000 value in another column in a column of records. Basically, my question should be a blend of the two:

UPDATE tmp
SET Col01 = 'top1000'

and

SELECT TOP 1000 Col01
FROM tmp
ORDER BY Col02 DESC

Example 4:

UPDATE TOP (@MaxRecords) Messages 
SET    status = 'P' 
OUTPUT inserted.* 
FROM   Messages 
where Status = 'N'
and InsertDate >= GETDATE()

In the Messages table there is priority column and I want to select high priority messages first. So I need an ORDER BY. But I do not need to have sorted output but sorted data before update runs.

With TIES Specifies that extra rows from the base result set be returned as the last of the TOP n (PERCENT) rows, with the same value in the ORDER BY columns. TOP... Only SELECT statements can employ WITH TIES, and only if the TOP and ORDER BY clauses are used.

Although the TOP...WITH TIES clause simplified our code, it underperformed when compared to our regular code. This was caused only by the ORDER BY phrase. A well-defined index can be used to control this bad performance.

For Example:

SELECT TOP(1) WITH TIES * FROM #MyTable
ORDER BY Purchase_Date

Example 1: Using Top clause

USE AdventureWorks2012_CS;
SELECT TOP(6)
 ProductNumber, Name, ListPrice,
 CONVERT(VARCHAR(10),SellStartDate,1) SellStartDate
FROM Production.Product
ORDER BY ListPrice DESC

Example 2: To limit the amount of rows updated or deleted in a statement, use TOP (x) with UPDATE and DELETE statements. However, I've never had to utilise SELECT TOP WITH TIES.

Any row with a column value that matches the last row's column value as indicated by the ORDER BY clause will also be returned in the results of SELECT TOP (5) WITH TIES. As a result, you get more rows than the TOP specifies (x).

now execute using WITH TIES

SELECT TOP (5) WITH TIES FirstName, LastName, FavouriteColour
FROM FamousTransformers
ORDER BY FavouriteColour

Output:

Example 3: Example of Top 10 WITH TIES

Let us look at one basic example to see how this clause works. If we have 100 rows in the table and 50 of them have the same value in the column that is used in ORDER BY, using TOP 10 rows will only return 10 rows, but using TOP 10 WITH TIES will return all the rows that have the same value as the last record of the top 10 — a total of 50 records.

SELECT TOP 10 WITH TIES *
FROM Sales.SalesOrderDetail
WHERE OrderQty = 1
ORDER BY OrderQty
GO

To supply row numbers to your table data, use CTE (partitioned by the empName). You can then update the cte, which has the added benefit of updating the base table.

Example 1: TransactionId, AssetType, AssetDetails, Order. Primary key is TransactionId, AssetType and Order.

'House' is one of the current assetTypes. Many residences can be involved in a transaction (Hence order is part of the key). I'm attempting to change the AssetType of the first house in each transaction to 'Home' (i.e., where AssetType is now = 'House' and Order is the minimum 'Order' for assetType House).

UPDATE TOP 1 tbl_ApplicantAsset
SET AssetType = 'Home'
FROM         Asset
WHERE     (tbl_Asset.AssetType = N'House')
GROUP BY Asset.TransactionId

Example 2: To supply row numbers to your table data, use CTE (partitioned by the empName). You can then update the cte, which has the added benefit of modifying the base table.

No, because you don't have an ordering column, there's no certainty which row will be updated (you'd want more restricted sorting constraints around this), but you can use the following as a starting point.

Step 1: create table

DECLARE @Table1 TABLE
    (
      empName VARCHAR(100) ,
      fieldtoupdate VARCHAR(10)
    );

Step 2: Insert record

INSERT  INTO @Table1
        ( empName )
VALUES  ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Green' ),
        ( 'Green' ),
        ( 'Green' );

Step 3: The solution

WITH    cteTop1Table ( RowNum, empName, fieldtoupdate )
   AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY empName ORDER BY empName ) AS RowNum ,
   empName ,
   fieldtoupdate
   FROM     @Table1
   )
    UPDATE  cteTop1Table
    SET     fieldtoupdate = 'Top 1'
    WHERE   RowNum = 1;

Step 4: display records

SELECT  *
FROM    @Table1;

Output: