6
Reply

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

Shivprasad Koirala

Shivprasad Koirala
13y
7.2k
0
Reply
    sarika jain

    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
    0
    sarika jain

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

    sarika jain
    13y
    0
    Ashish kandhowa

    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
    0
    Shivprasad

    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
    0
    sarika jain

    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
    0
    Shivprasad Koirala

    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
    0
Message