Total Page Preview:   000000011567

How to get the alternate rows or records from table in sql server

In this article I am going to explain How to get alternate row from table in sql server with expample using sample and CTE query.

Get the alternate row or record to Gender column:

---------------------Get the alternate row by the Gender--------------------
 
SELECT NAME, GENDER, ROW_NUMBER() OVER(PARTITION BY GENDER ORDER BY GENDER) AS R
FROM #PERSON ORDER BY R, GENDER DESC
 

Get the alternate row or record to Gender column Using CTE:

---------------------Get the alternate row by the Gender using CTE--------------------
 
;WITH PRS (Name, Gender, R)
AS
(
SELECT NAME, GENDER, ROW_NUMBER() OVER(PARTITION BY GENDER ORDER BY GENDER) AS R
FROM #PERSON
)
SELECT NAME, GENDER FROM PRS ORDER BY R, GENDER DESC
 
 
 
 
 
 
 
Get SQL Scripts for Create Tables and Insert Data for demo:
 
---------------Create table ------------
CREATE TABLE #PERSON
(
     NAME        VARCHAR(60),
    GENDER    VARCHAR(1)
)  
 
--------------------------------- Insert record into table------------------------
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Brijesh', 'M')
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Rahul', 'M')
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Saurabh', 'M')
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Arti', 'F')
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Jyoti', 'F')
INSERT INTO #PERSON (NAME, GENDER) VALUES ('Preeti', 'F')
 
 
 
Thanks !!!
Brijesh Kumar
 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments

DHRUV
14-Apr-2021
hi Brijesh , Request you to please explain the behaviour of the query.

                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: