Nth Highest Salary Query in Oracle 9i/10g/11g/11i

Problem: Show the list of all employees from the employee table having second highest Salary or Nth highest salary in the organization.

Solution: Well we can achieve the result by so many ways but in this post I would like to do it by using co-related queries.
Query to get the list of employees getting 2nd highest salary (Set the value of @SalaryPosition to get Nth highest salary):




SELECT * FROM Employee E1  WHERE @SalaryPosition = ( SELECT COUNT(DISTINCT E2.Salary) FROM Employee E2 WHERE E2.Salary >= E1.Salary )



Co-Related Query:  In short, it’s a type of nested sub query where the inner query is referenced to some value of the outer query. And the inner query is executed for each record in the outer query making it slowL.
     In our query, to get the 2nd highest salary, works in the same way too. Let’s dive a little deeper.

Sample data used in employee table:


slNo           empname               salary


101            Ram                      5000
102            Abhishek              7500
101            kumar                   5000
104            prasad                  6570
102            Jumla                    7500
101           Harkesh                12000
101           John                      4000

So we have taken two aliases for the employee table i.e. E1 and E2.(Here N=2)

Corollary: Let’s take the first record in the table E1 where the salary is 5000 and assume it as the second highest salary. But how will you know if it is actually the second highest salary or not. If 5000 is the second highest salary, then the distinct count of all the salaries from table E2 which are greater than or equal to 5000 must be 2.

The first record from E1 (salary = 5000) will be compared to all the records in E2 (salary).


Table E1                                         Table E2                                                  Distinct count where
(considering the first record only)   (distinct salary)                                           salary is >= 5000

5000                                               4000                                                                   4
                                                       5000
                                                       6570
                                                       7500
                                                      12000

Hence 5000 is not the highest salary as we have 4 more salaries greater than equal to 5000. But it is concluded that 5000 is at the fourth position.

     Now let’s take the second salary from table E1 and do the same comparison as in the earlier case.

The second salary is 7500 and as per rule the count of distinct salaries greater than equal to 7500 must be 2.

   Table E1                                         Table E2                                                Distinct count where
(considering the second record only)  (distinct salary)                                       salary is >= 5000


7500                                                  4000                                                                 2
                                                          5000
                                                         6570
                                                         7500
                                                         12000


Here in this case our condition in the where clause of outer query is satisfying.

Note:-Please comment and reply me.

Read More

If id is null, use the value from sequence

If id is null, use the value from sequence
 
SQL> create table myTable(
  2 id number primary key,
  3 blob_content blob
  4 )
  5 /

Table created.

SQL>
SQL> create sequence myTable_seq
  2 /

Sequence created.

SQL>
SQL> create or replace trigger biu_myTable
  2 before insert or update on myTable
  3 for each row
  4 begin
  5 if :new.id is null then
  6 select myTable_seq.nextval into :new.id from dual;
  7 end if;
  8 end;
  9 /

Trigger created.

SQL> drop table myTable;

Table dropped.

SQL> drop sequence myTable_seq;

Sequence dropped.




Note:-Please comment and reply me.

Read More

A Complete Guide to Google Special Search – How to Use Google A Lot More Creatively (Part I)



Google is no doubt the most popular search engine till date. Millions of people across the globe use it everyday to search for content, pictures, blogs and everything else. The reason of the huge popularity of Google is because the results are so relevant and match closely with what the user is searching for.

Apart from regular search queries, do you know that you can use the Google search engine in a variety of creative ways? You can use Google to know the sunrise and sunset timings, convert currency amounts, perform local searches, find movie show times and so on. These type of Google search, better known as Google special search requires the user to type a specific query and add an attribute to the query.

A very good example of a special Google search is knowing the time of a particular city. I live in India and want to know what time is it in London, UK. To perform this Google special search, I will have to type the exact query as “What time is it” and supply the parameter as “London”

What’s interesting to note is that these Google searches depend upon your geographic location (in some occasions) and the results are displayed accordingly.

In this article, we look into some special Google searches which can be used in a more creative way to know important things you have always wanted:
Know the Weather of a Any City Using Google

To know the weather conditions of any city, go to Google.com and enter the name of the city in the search box. Then type the state and the country of the city, as shown in the following example:



Not only will Google display the current average temperature of your city, but you can also see the weather pattern for the last week. Additional details like humidity and wind speed are also shown in the same page and you can add the result to your iGoogle home page using the link “Add to iGoogle”.

Know the Time of Any City Using Google

Similarly you can also know the present time of any part of the world using a simple Google search. Just type time in the Google search box followed by the name of the city or country as shown in the following example:

Read More