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 |
|
hai
Yak Posting Veteran
84 Posts |
 Posted - 2008-06-26 : 21:44:30
|
| id name qty amount1 user1 3 02 user2 3 03 user1 2 34 user3 2 05 user3 2 16 user2 1 17 user1 2 38 user3 3 09 user1 3 3result id name 9 user1 6 user2 5 user3 select * from(select *, rank() over (Partition BY name order by qty DESC, amount desc) as Rankidfromtable1) tbl1where rankid=1this is not giving me the result. Any help is greatly appreciate.thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-06-26 : 22:12:45
|
your ranking is by qty desc, amount desc. for user2 that will give you id = 2. Why are you expecting id = 6 ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
hai
Yak Posting Veteran
84 Posts |
Posted - 2008-06-26 : 22:17:32
|
| Because the amount is 0, although the qty is 3, if amount is zero, then go to the next one. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-06-26 : 22:24:39
|
quote:
Originally posted by hai
Because the amount is 0, although the qty is 3, if amount is zero, then go to the next one.
"Partition BY name order by qty DESC, amount desc"So what is the rule that you want ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
hai
Yak Posting Veteran
84 Posts |
Posted - 2008-06-26 : 22:42:51
|
| if qty have amount other than 0 select the highest qty, otherwise select the lower qty that have amount. if all the qty have 0 amount, than select the highest qty for the same name.I know my query is not correct, and thank you for helping. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-06-26 : 23:02:35
|
[code]Partition BY name order by case when amount <> 0 then 1 else 2 end,qty desc,amount desc [/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
duplicated with criteria...