PayPal Interview Question
-- solution #1: nested top
SELECT TOP 1 FName
FROM
(
SELECT TOP 10 FName
FROM Names
ORDER BY FName
) sub
ORDER BY FName DESC
-- solution #2: NOT IN
SELECT TOP 1 FName
FROM Names WHERE FName NOT IN
(
SELECT TOP 9 FName
FROM Names
ORDER BY FName
)
ORDER BY FName
-- solution #3: derived count
-- this assumes FName is unique
SELECT FName
FROM Names
WHERE
(
SELECT COUNT(*)
FROM Names n2
WHERE n2.FName <= Names.FName
) = 10
-- solution #4: MAX
SELECT FName = MAX(FName) FROM
(
SELECT TOP 10 FName
FROM Names
ORDER BY FName
) sub
(select * from (select rownum as rnum, First_name||' '||Last_name as name from employees) where rnum=31)
here you are actually creating a table in the sub query in which the rownumber is adding up as rnum and in the main query you are selecting that rnum value to what ever the n value is ..
This question is actually asked to test your abilities in writing sub queries....
select * from (select rownum r,tablename.* from tablename) where r=n;
n is required row number
SELECT fistname, lastname
- kaustubh deshmukh September 18, 2012FROM employee
limit 3,1
gives 4th row, if you want 8th row then replace 3 by 7