SQL Server interview question :- Select record with the second lowest value from the below customer table?

Shivprasad Koirala

13y

7.2k

There is another query to get the second lowest value from this table

--To get the entire row with the second lowest value from the table

select * from FindSecondGreater where Amount=(select min(Amount)

from FindSecondGreater where Amount not in

(select min(Amount) from FindSecondGreater))

sarika jain

13y

No it is not wrong it is giving the exact result.Try again..............

sarika jain

13y

to get second lowest amount

select min(amount) from findsecondgreater

where amount not in (select min(amount) from findsecondgreater)

to get full row

select top 1* from findsecondgreater where

amount not in (select min(amount) from findsecondgreater)

Ashish kandhowa

13y

There is something wrong in the second query. I executed the same did not give me exact results.

My 500 .NET interview questions and answers

Shivprasad

13y

We can get the second lowest value from the above table

just go through this:

create database Greater

use Greater

create table FindSecondGreater

(

Code int,

StudentName varchar(50),

Amount money

)

insert into FindSecondGreater values(1001,'pqr',200)

insert into FindSecondGreater values(1002,'raju',100)

insert into FindSecondGreater values(1003,'shiv',300)

insert into FindSecondGreater values(1004,'shaam',400)

select * from FindSecondGreater

--To Find only 2nd lowest Amount

select min(Amount) from FindSecondGreater where Amount

Not In(select min(Amount) from FindSecondGreater)

--To get the entire row which has the second lowest salary

select * from FindSecondGreater f1 where 1=(Select count(distinct Amount) from FindSecondGreater f2

where f1.Amount>f2.Amount)

sarika jain

13y

Answer:

Below is a simple customer table with 3 fields code , name and amount. Can you select the customer with second lowest amount. This is one of the favorite questions which is asked to test your SQL Server skills.

Code	Name	Amount
1001	pqr	200.00
1002	raju	100.00
1004	shiv	300.00
1007	shaam	400.00

Below is the answer. It can be accomplished by using sub queries. First select the min and then get all records which are more than the min and select top 1 from the same. Below goes the query.

select top 1 * from tbl_Customer touter where touter.Amount > (SELECT 
min(tinner.Amount)
  FROM [Customer].[dbo].[tbl_Customer] as
 tinner)
  order by touter.Amount asc

Some time interviewers can trick you questions like select the second top. In the subquery we just need to change the same to max.

Get 200 SQL Server Interview questions and answers or .NET Interview questions and answers

Shivprasad Koirala

13y