fetch

SQL query to find first record and last record in single query



Sharing buttons:

hello everyone welcome to SQL expert in

this video we are going to see how to

find out or how to retrieve first record

and last record even though if you have

one lakh records or 2 lakhs records how

you are going to find out first record

and last record I am going to explain

step by step so I request you to

subscribe our channel and watch this

complete video so here I am having a

table called employee ok so I am having

six records then it is just for our

practice purpose I have created this

table so I am going to display the first

record and last record I am going to

explain you how this is going to work

how we have to understand the sequel

query so I request you to understand the

logic so if you see here now the

employee ID the first record employee ID

is 7 3 6 9 how commenter this one the

last record employee ID is 7 5 to 1 we

can keep name also this is ID and we'll

keep name first is a Smith and here name

last record is work so just I am taking

only last name so later you guys can

practice after looking this so we are

going to find out first record and last

record based on

remember okay row number is dynamic as

you guys I hope you guys know that on

top of on top of query on top of the

result our roll number is going to

generate C for example now I have

selected I have selected row number from

employee let me worry so I'm having six

record so I'm having six rows so what it

is going to do is let me explain with

details so if you see here I'm having

six records so let us free tour this

where job ID not in we will take out

eight three records from this six seven

one even though we can use so job ID

equal tall so it is not required now

anyway we have three cards

600 so let me execute this first we will

go ahead and see over here this is job

ID underscore job underscore ID if I

execute here I'm getting firstly card

and lastly cards okay if you see here

employee table only we will get six six

seven and six seventy and 670 only three

records we are going to get first to the

two Regas and last one because so total

we will get three records you can see

here total we get three record even

though the records is from first two and

last one if you look into the row number

it is going to generate a sequence one

two three so this means that whenever we

write a sequel query for the outward

whatever we are going to get after using

all the Frieda conditions the row number

is going to generate okay that's what

you have to understand so now let us try

select select row number select star

from employee where row number is equal

to 1 C so what it is going to do is it

is going to retrieve it is going to

display the first record from the table

so you can see here seven three six nine

seven three six nine Smith Smith so we

have retrieved we have retrieved first

record from the table even though there

are n number of records in the table we

can do it like this okay so now what we

need is we need last record from the

table so what we are going to do is here

select star from select star from

employee where first we have to give

order order by order by row number

descending so what it is happened is if

you look into here actually this is the

first record 7 3 6 9 if you see here 7 3

6 9

okay it is going down okay so based on

the boy ID we are descending we are

descending that complete we are

descending the complete records so even

though if you have one lakh records the

1 lakh record is come to first place it

may be 10 million record one CRO record

whatever may be it

we'll come to first place and other

records will go down the first record we

go to last place so here what we are

doing is we are descending we are

descending based on the row number and

on top of this we are writing another

query select star from bracket off so

this output this output this output this

query output will be use it for this

query okay this is a sub query so what

we are waiting here select star from

this complete area considers as a table

where row number is equal to one so what

it is going to do is it will display

last regard lost Picard so let me show

you so if you see here this is the last

occurred seven five to one here's a walk

you can see here walk okay so we are

able to fetch first record using one

query we are able to fetch another last

card one query we have to make it one

query just to have to write Union Union

all so that we are going to display all

the records I mean both the records

first record and maximum recorded

minimum record from the table if you

look at here seven three six nine is the

first occurred and seven five two one is

the last record so like this we can

display both the records from the table

first record and the last card

thank you all for watching this video

I'm going to create a lot of videos for

you guys for sharing knowledge purpose

please subscribe our channel and share

it to your friends this will be very

very helpful for you guys thank you all

for watching this video