Answer Posted / palash

question is which db are we looking at..
sqlserver or oracle

if sqlserver then top function is readily available to get
the nth highest sal etc

what if it the db is oracle, oracle does not have a
implicit top function to use so how do we go about it

couple of ways

1) use analytical queries
2) use co-related queries (suitable in small sized
databases)

1) analytical queries

if looking for nth highest within the complete table

select * from (select sal , dense_rank() over(order by sal
desc) rnk from emp ) where rnk = n

we can use row_number/rank functions also in place of
dense_rank.


if looking for nth highest within each department.

select * from (select sal, dense_rank() over (partition by
dept order by sal) rnk from emp) where rnk = n


2) co-related queries:

select sal from emp e1 where (n-1) = (select count(1) from
emp e2 where e2.sal > e1.sal)

this query will be pretty slow if the size of the table is
huge.

so my advice is to use the analytical version which is much
much faster than the co-related version.

Does server sql treat char as a variable-length or fixed-length column?

1034

---

What is the difference between a "where" clause and a "having" clause?

1160

---

Do I need a report server to run reports in my application?

165

---

What are the built in functions in sql server?

985

---

How is table type constraint applied to a table?

979

---

Explain the different index configurations a table can have?

947

---

What is difference between inner join and join?

1044

---

What is data compression? : sql server database administration

976

---

What is bit data type? What's the information that can be stored inside a bit column?

921

---

What is trigger explain with program?

983

---

What is the Disadvantage of indexed sequential file.

1084

---

what is datawarehouse?

1114

---

How to send a ssrs report from ssis?

176

---

What is recompile sql server?

1040

---

What is ms sql server service broker?

993

---