How to fetch alternate records from a table in sql
How to fetch Alternate Records from the Table | SQL Interview Question
Timecodes:
[Music]
hello friends this is sunil and welcome
back to my channel it junction for all
in this video i am going to discuss a
very basic sql interview question that
is how to fetch
alternate records from the table
so we have the input table over here
that is the students and from this
student table we have to fetch alternate
records from the table when we say
alternate records this alternate records
can either be
or even number records or
odd number records so when i say even
number records it can be row number two
row number four no row number six like
that when i say odd number records it
will be row number one row number three
row number five like that so
this is our requirement let's see how we
can do this in sql server
we are going to solve this problem by
using
window function and modulus operator so
let's start by writing select
star
from our table name is students
now let me find this query
okay now i'm going to replace this star
with the column names so id we need name
column we need even gender
after that i am going to derive
another column by using
window function
so for that i am going to use
row number
after that i am going to use over clause
here we need not to partition on any
column we just need to do order by
id column
and will give alias as ranked
so we have derived another column
called rank
so let me find this small piece of code
so we have got another column called
rank
and we have got the ranking for each row
based on the id column
now what we have to do
we have to use modulus operator
on this column rank column
so now
i'm going to
convert this
sql query into inner query
now i'm going to write
a sub query
so select
we need this column id column name
column and gender column so i'm going to
select this and i need to paste it over
here
and then
from where i need i need from this in a
sub query so i'm going to open the
bracket and i'm going to close away over
here and then i'm going to give alias as
a
after that i'm going to use where clause
where
rank
modulus
2
should be
equal to 0
so what does this modulus operator do it
gives the reminder so what it will do it
will uh it is
it is going to divide by 2 on the rank
column and
it will provide the remainder so
wherever
after dividing the rank column if the
demand remainder is zero
it is going to return that rule so let
me fire this query
so you you can see we have got all the
even numbered row
similarly if i am going to copy this
piece of code and i am going to paste it
and instead of 1 0
here if i am going to put 1 remainder 1
will will get the all the
odd uh odd row right so all we have got
alternate rows that are ordinal or the
odd rows so this is how we can
uh write this sql query to fetch
alternate records from the table if you
have liked my video please do like it
and do subscribe my channel and if you
know any other way by which you can
solve this problem please do write in
the comment section thank you
Related queries:
how to get alternate records from a table in sql
how to fetch alternate rows from the table in sql
how to fetch records from two tables in sql
how to fetch alternate records from a table
how to fetch alternate records from a table in mysql
