Home » SQL & PL/SQL » SQL & PL/SQL » Find 3 most current dates
Find 3 most current dates [message #303838] Mon, 03 March 2008 00:26 Go to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I have to select the data where the exportdate column has the 3 most current dates for which I am using a rank column.
I can later select only those rows where rank is less than 4.

The data required for rank column should be like this :
exportdate         item      loc           rank
2/3/2008 0:05	10006020   bath           1
2/3/2008 0:05	10006021   bath           1
2/3/2008 0:23	10006022   bedding        2
2/3/2008 0:23	10006026   bedding        2
3/3/2008 0:12	10006060   bath           3
3/3/2008 0:12	10006078   bath           3
3/3/2008 0:56	10006089   bedding        4
3/3/2008 0:56	10006090   bedding        4


I am trying the code
select exportdate, item, loc from
(
select exportdate, item, loc ,rank() over(partition by exportdate order by exportdate desc)rank
from test) 
where rank < 4


But this is giving the result as rank only 1 for all records.


If I use the code removing PARTITION BY exportdate in the above code:
select exportdate, item, loc from
(
select exportdate, item, loc ,
rank() over(order by exportdate desc)rank
from test) 
where rank < 4

Then the output for Rank column comes as
exportdate         item      loc           rank
2/3/2008 0:05	10006020   bath           1
2/3/2008 0:05	10006021   bath           1
2/3/2008 0:23	10006022   bedding        2
2/3/2008 0:23	10006026   bedding        3
3/3/2008 0:12	10006060   bath           4
3/3/2008 0:12	10006078   bath           5
3/3/2008 0:56	10006089   bedding        6
3/3/2008 0:56	10006090   bedding        7


Please suggest a way to get ranking in order of exportdate.

Thanks,
Soni

[Updated on: Mon, 03 March 2008 00:29]

Report message to a moderator

Re: Find 3 most current dates [message #303842 is a reply to message #303838] Mon, 03 March 2008 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But this is giving the result as rank only 1 for all records.

As you partition by "exportdate" each partition contains only one date and so each row is at rank 1.

Regards
Michel


Re: Find 3 most current dates [message #303847 is a reply to message #303842] Mon, 03 March 2008 00:40 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

How should I do it to get the ranking according to the exportdate as and when the exportdate changes in the record...the rank should change too.
Re: Find 3 most current dates [message #303849 is a reply to message #303847] Mon, 03 March 2008 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to understand what does the "partition" clause mean.
Why do you put this clause?
If you don't need to partition your source (for instance per location) then you don't need to use this clause.

Regards
Michel
Re: Find 3 most current dates [message #303851 is a reply to message #303847] Mon, 03 March 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm just seeing you already did it in your second query.
What does not work in this one for you?

Regards
Michel

[Updated on: Mon, 03 March 2008 00:46]

Report message to a moderator

Re: Find 3 most current dates [message #303854 is a reply to message #303851] Mon, 03 March 2008 00:49 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

The second query is resulting in rank column to give the value 1,1 for first two rows which is correct but from 3rd row onward it is giving value for rank column as 2,3,4,5,6,7.

But the required result for rank column should 1,1,2,2,3,3,4,4.

Please advice.

[Updated on: Mon, 03 March 2008 00:50]

Report message to a moderator

Re: Find 3 most current dates [message #303864 is a reply to message #303854] Mon, 03 March 2008 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dense_rank.

Regards
Michel
Re: Find 3 most current dates [message #303868 is a reply to message #303864] Mon, 03 March 2008 01:14 Go to previous message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Thanks Michel,
It has worked.

Regards,
Soni
Previous Topic: numeric value converted to wordings
Next Topic: Get data from Multiple Columns to one column
Goto Forum:
  


Current Time: Sun Dec 04 06:26:53 CST 2016

Total time taken to generate the page: 0.09440 seconds