Header Ads Widget

SQL Interview Questions on ROW_NUMBER() function in sql

Have you ever came across any scenario when you used ROW_NUMBER() function in SQL queries?

Not sure ? Don't worry, in today's article I will tell you about this very function. Most importantly, you can use it while you are describing any challenging situation in any of the interview. You will be at advantage position than others.
SQL Interview Questions

I will first give you a scenario and I will ask you to write SQL queries for that. So here is the scenario.

You have one table filled with Item information like,
Table name : ITEM_HEADERITEM_NO, ITEM_DESCRIPTION, ITEM _SUPPLIER, CREATE_DATE

Now you have one more table:
ITEM_DETAILITEM_NO, QTY, COST_PRICE, SELLING_PRICE and ACTIVE_DATE. 

There can be multiple prices for similar item in ITEM_DETAIL table based on there active date. I just want to fetch only those prices for every item which is having latest active date.

So Fetched column would be: ITEM_NO, ITEM_SUPPLIER, QTY, COST_PRICE, SELLING_PRICE.
Take care there should not be any duplicate present in extracted data. So now try this scenario, then read below solution. There can be multiple way to achieve this. So I am answering in a way which I used when I had similar situation.

Solution:

SELECT * 
  FROM (SELECT
          a.item_no,
          a.item_supplier,
          b.qty,
          b.cost_price,
          b.selling_price,
          ROW_NUMBER() OVER (PARTITION BY a.item_no ORDER BY b.activate_date desc) selling_price_rank
        FROM
             item_header a, 
             item_detail b 
       WHERE a.item_no=b.item_no)
  WHERE selling_price_rank=1;

 This is something which can put you at advantage during SQL interviews. If you are working as SQL developer then you will get into such situation and you will be trying many things like aggregate functions( use of MAX()). Even you will give a try to use max function in where clause also. But this row_number() and partition by function will solve your issue quickly.
Now a days many interviewer ask questions based on rank, rownum etc. You can try solving above using dense_rank(), rank() also but for that you will have to add some more conditions also. Meanwhile you can try, I will come with new facts about dense_rank() and rank() in subsequent articles.

I am now wrapping up this article with one open question. Please answer that in comment.
" Can we find fifth number row of any data set using ROWNUM ?"
Till then keep structuring your queries in you favorite Language.

Post a Comment

0 Comments