Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
martind1
Starting Member
28 Posts |
 Posted - 2012-04-24 : 04:51:50
|
| Hi,I have a query that I am using a ranking function on.My rank is as follows:DENSE_RANK() over (Partition BY LocationID ORDER BY [Price] ASC) As RankNow my understanding was that if there were two results with the same Price one would be given a rank of 1, and the other 2?If thats is not the case, how can I get it to do this?Thanks in advance.--http://www.tutorial-resource.com - Free Web Development Resources |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-04-24 : 05:06:37
|
quote:
Now my understanding was that if there were two results with the same Price one would be given a rank of 1, and the other 2?
Nope. According to BOLhttp://msdn.microsoft.com/en-us/library/ms173825.aspxBoth of the Price will get Rank 1 and the next higher price Rank 2 (within the same LocationID)Maybe row_number() is what you want ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
martind1
Starting Member
28 Posts |
Posted - 2012-04-24 : 05:20:35
|
| Ah Okay, my bad on reading it wrong.Ive tried ROW_NUMBER but isnt of much use. Would there by any other way around this?--http://www.tutorial-resource.com - Free Web Development Resources |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-04-24 : 05:24:38
|
maybe you can explain what do you want actually ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
martind1
Starting Member
28 Posts |
Posted - 2012-04-24 : 05:31:07
|
| I am trying to return a set of results which are Location unique. Then I want the cheapest price for that location.So I want:-----LocationOne £100LocationTwo £105LocationThree £150LocationFour £163But my result set is returning:-----LocationOne £100LocationOne £100LocationThree £150LocationFour £163Because there are two prices for LocationOne with the same price. I onlt want to show one price per location.--http://www.tutorial-resource.com - Free Web Development Resources |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-04-24 : 05:32:32
|
row_number() will work !ROW_NUMBER() over (Partition BY LocationID ORDER BY [Price] ASC) As Rank
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
martind1
Starting Member
28 Posts |
Posted - 2012-04-24 : 05:37:54
|
| Oops.I was thinking of it the wrong way.Thanks for the help bud, worked perfectly!:)--http://www.tutorial-resource.com - Free Web Development Resources |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-04-24 : 05:42:42
|
welcome
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
Rank problem