SQL Interview Questions | Alternate Rows

Question

You have data of people having applied for a lottery ticket. The data consists of their name and ticket number. You have to choose winners by selecting the people present in the alternate rows (first winner starting from row number 3). Write a query to make things easy to select the winners.

(Hint- Choose alternate rows, beginning from row number 3)

in progress 4
Dhruv2301 4 years 21 Answers 5501 views Great Grand Master 6

Answers ( 21 )

  1. swap007
    1
    Reply

    SELECT *
    FROM (
    SELECT *, Row_Number() OVER(ORDER BY ticketno) AS RowNumber
    FROM Lottery
    ) t
    WHERE t.RowNumber % 2 = 1
    OFFSET 2 rows

    -1
    Reply

    select * from
    (select name,ticket number,
    row_number() over(order by ticket number))
    where ticket_number>=3 and ticet_number %2!=0

  3. Yash Bagaria
    3
    Reply

    select *
    from (select name, ROW_NUMBER() over (order by ticket_no) as srNo from db) t
    where (t.srNo % 2) = 1

  4. vinod.k
    1
    Reply

    select name,ticket_number from (
    select name,ticket_number,row_number() over (order by ticket_number) as r_num from table) t
    where t.r_num >=3 and t.r_num%2=1;

  5. Sai
    -6
    Reply

    Select Row_Number() OVER( ORDER BY Ticket Number)as Rownumber,
    Name,Ticket_Number
    from Lottery
    where Rownumber >3 and Rownumber/2 !=0;

  6. Ognish
    1
    Reply

    select name as winner
    from
    (select name , ticket_number, row_number() over(order by ticket_number from table)) t1
    where t1.row_number >= 3 and t1.row_number%2=1

  7. VigneshGV91
    0
    Reply

    With CTE as
    (Select
    name
    ,Row_number() over (order by select Null) as rn
    from table)

    Select
    Name
    from CTE
    where rn%2=1
    order by rn
    offset 1 ROWS

  8. Himanshu Sharma
    1
    Reply

    WITH lottery AS
    (SELECT
    name
    ,ticketnumber
    ,ROW_NUMBER() OVER (ORDER BY ticketnumber) AS rn
    FROM lottery)

    SELECT
    name
    ,ticketnumber
    FROM lottery
    WHERE MOD( rn ,2) = 1 AND rn != 1

  9. Amit
    0
    Reply

    WITH CTE AS (SELECT * FROM
    (SELECT NAME,TICKET_NUMBER
    ROW_NUMBER()OVER()
    FROM HR_EMPLOYEES
    ) SD
    WHERE ROW_NUMBER >= 3
    )
    SELECT * FROM CTE
    WHERE ROW_NUMBER % 2 0

    0
    Reply

    select name as winner from
    (select name, ticket_no, row_no() over (order by ticket_no from lottery)) lottery
    where ticket_no. >=3 and ticket_no % 2 != 0

  11. NaveenaParasa
    0
    Reply
    SQL Interview Questions | Alternate Rows

    Sample table created with name Lottery

    0
    Reply

    SELECT *
    FROM
    (SELECT name,ticket number,
    row_number() OVER (ORDER BY ticket number))
    WHERE ticket_number>=3 and ticket_number %2!=0

    0
    Reply

    select * from(
    select name,ticket, row_number() over (order by ticket) as r from table)t where r>=3 and r%2!=0;

  14. Rakesh Mehta
    0
    Reply

    select employee_id
    from
    (
    select employee_id, first_name, department,
    row_number() over() as row_num from employees
    ) A
    where row_num >=3
    and row_num%2 = 1

    0
    Reply

    select name, ticket_no
    from
    (
    select name, ticket_no,
    row_number() over() as row_num from lottery
    ) A
    where row_num >=3
    and row_num%2 = 1

  16. Rakesh Mehta
    0
    Reply

    select name, ticket_no
    from
    (
    select name, ticket_no,
    row_number() over() as row_num from lottery
    ) A
    where row_num >=3
    and row_num%2 = 1

  17. vivek_bharath_k
    0
    Reply

    SELECT row_, name, ticket_number
    FROM ( SELECT row_number() over ( partition by name ) row_, name, ticket_number
    FROM lottery_table ) t
    WHERE row_>=3, row_%2!=0

  18. kethireddimohit3643
    0
    Reply

    SELECT *
    FROM (
    SELECT *, Row _Number() OVER(ORDER BY ticket no) AS Row Number
    FROM Lottery
    ) t
    WHERE t.RowNumber % 2 = 1
    OFFSET 2 rows

Leave an answer

Browse
Browse