Scenario is to find out the sales figure for each store based on their actual sales.
SELECT store_no, sale_month, actual_sale,
RANK () OVER (PARTITION BY store_no ORDER BY actual_sale) as SaleRank
FROM sales;
Note that there is nothing being fed to the function RANK. Result set below shows the sales figure ranked, for each store.
store_no sale_month actual_sale SaleRank
10 05/01/2007 13,500 1
10 01/01/2007 13,500 1
20 02/01/2007 11,000 1
20 04/01/2007 12,500 2
30 02/01/2007 10,000 1
30 03/01/2007 10,500 2
30 03/01/2007 12,500 3
40 02/01/2007 11,500 1
40 04/01/2007 12,500 2
50 01/01/2007 10,500 1
50 05/01/2007 12,500 2
60 06/01/2007 11,500 1
70 07/01/2007 11,500 1
80 07/01/2007 15,500 1
90 06/01/2007 15,500 1
ROW_NUMBER ():- It returns the sequential number of the row within a group, starting with 1 based on the ORDER BY clause. With the above scenario if we replace the RANK with ROW_NUMBER and run the following query:
SELECT store_no, sale_month, actual_sale,
ROW_NUMBER() OVER (PARTITION BY store_no ORDER BY actual_sale) as SaleRank
FROM sales;
The result set we get is similar, only difference is for first two rows we have 1,1 with RANK but 1,2 with ROW_NUMBER. This is because ROW_NUMBER takes into consideration the sequential occurrence of the row in a group which is store_no here.
store_no sale_month actual_sale SaleRank
10 05/01/2007 13,500 1
10 01/01/2007 13,500 2
20 02/01/2007 11,000 1
20 04/01/2007 12,500 2
30 02/01/2007 10,000 1
30 03/01/2007 10,500 2
30 03/01/2007 12,500 3
40 02/01/2007 11,500 1
40 04/01/2007 12,500 2
50 01/01/2007 10,500 1
50 05/01/2007 12,500 2
60 06/01/2007 11,500 1
70 07/01/2007 11,500 1
80 07/01/2007 15,500 1
90 06/01/2007 15,500 1
Our Random Articles
- Somdev Devvarman ousts Xavier Malisse 6-1, 3-6,7-6(5)
- How To Choose A Good Online Traffic School
- New Year Wishes 2010
- New Year Wishes
- How to use EXTRACT function with date and time columns
More Links
4 Comments
Material that you are providing is very easy to learn. But it is much better than this , if you ll give the full material of this at ONE LINK.
Thank you
Suresh.
That is right Suresh. This is the reason I have given all the links in one page, please visit this
http://readvitamin.com/teradata/
Hope this helps.
Hi. Can anyone tell me how to extract time from timestamp?
For Example
timestamp=’2008-03-28 14:11:04′
i want time ’14:11:04′ from timestamp through query.
Thanks in advance.
Hi Vaideesh,
If you need only time, you can probably try the following;
SELECT time;
To answer your question you should do something like this.
SELECT CAST(VaideeshTime as time(0)) from timestamp;
VaideeshTime
——–
14:11:04
Hope that helps.