SQL Use ORDER BY With TOP to Return the Top X Rows Based On a Column’s Value
Last Updated :
16 Nov, 2021
In this article, we are going to learn how to use ORDER BY with TOP to return the top x rows based on a column’s value. We will use another table to fetch the top value and use that value in another table.
So first we create a database.
Step 1: Create Database
CREATE DATABASE GFG
Step 2: Use this DB
USE GFG
Step 3: Create a table for storing the top value
We will create a table for storing top value so that we can use that to fetch from another table
CREATE TABLE TopValue(
val varchar (255)
)
Insert some top value data in this table
INSERT INTO TopValue VALUES(3)
Step 4: Create a data table that is to be fetched
Now we will create a table whose data is to be fetched and will fill dummy data
CREATE TABLE geeksforgeeks(
ID INT,
Name varchar(255)
Age varchar(5),
Dept varchar(255)
)
Insert some data in this table so that we can fetch this data
INSERT INTO [dbo].[geeksforgeeks]
([ID]
,[Name]
,[Age]
,[Dept])
VALUES (1, 'Devesh', 21, 'CSE')
GO
INSERT INTO [dbo].[geeksforgeeks]
([ID]
,[Name]
,[Age]
,[Dept])
VALUES (2, 'Aman', 25, 'IT')
GO
INSERT INTO [dbo].[geeksforgeeks]
([ID]
,[Name]
,[Age]
,[Dept])
VALUES (3, 'Jatin', 25, 'CSE')
GO
INSERT INTO [dbo].[geeksforgeeks]
([ID]
,[Name]
,[Age]
,[Dept])
VALUES (4, 'GFG', 23, 'CSE')
GO
INSERT INTO [dbo].[geeksforgeeks]
([ID]
,[Name]
,[Age]
,[Dept])
VALUES (5, 'Para', 22, 'CSE')
GO
Step 5: Now fetch the top data based on the other table value
DECLARE @TopVal INT;
SET @TopVal = (SELECT val from TopValue)
SELECT TOP (@TopVal) * FROM geeksforgeeks ORDER BY Age DESC
Output:
We can see the data is successfully fetched based on the top value from another table and ordered by age descending order.
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...