Total Page Preview:   000000012335

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.
Warrick
02-Oct-2021
I enjoy what you guys are up too. This type of clever work and exposure! Keep up the terrific works guys I've included you guys to my personal blogroll.
Bowes
10-Oct-2021
Excellent beat ! I wish too apprentice while you amejd your site, how could i subscribe for a blog site? The account aided me a acceptable deal. I had been tiny bit acquainted of this your broadcast provided bright clear concept
Click
26-Oct-2021
Hey! I'm at work browsing your blog from my new iphone 3gs! Just wanted to say I love reading your blog and look forward to all your posts! Keep up the excellent work!

                           
                           

                           

                           

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 unused indexes on azure SQL database scripts
02-Oct-2018
SQL Server - Find all index with key columns on Azure SQL Database
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
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-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
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
How many types of dimension tables available in data warehouse?
16-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
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: