ranking - need help

[xponcall]

select * into #table1 from (
select
49 rowid, 7373194 id, 3 ProdType, 200 Amount , 180 Qty union all select
49 , 106049 , 2 , 0 , 180 union all select
1096 , 5008960 , 1 , 0 , 120 union all select
1096 , 108891 , 2 , 0 , 120 union all select
1096 , 6627903 , 1 , 0 , 250 union all select
1096 , 109697 , 3 , 0 , 2500 union all select
927 , 109946 , 2 , 0 , 25 union all select
927 , 109927 , 2 , 0 , 128 union all select
927 , 109927 , 3 , 0 , 25 )B

select * from (
select a.*, Rank() over (Partition BY a.rowid order by
case when (a.amount <> 0 and a.prodtype=3)then 0 else a.amount end,
case when (a.Qty <> 0 and a.prodtype=2)then 0 else a.QTY end,
a.Prodtype DESC) as rankid
from #table1 a)b
--where rankid =1
order by rowid,rankid

result from query above:
rowid id ProdType Amount Qty rankid
49 106049 2 0 180 1
49 7373194 3 200 180 2
927 109946 2 0 25 1
927 109927 2 0 128 1
927 109927 3 0 25 3
1096 108891 2 0 120 1
1096 5008960 1 0 120 2
1096 6627903 1 0 250 3
1096 109697 3 0 2500 4

result needed
rowid Id ProdType Amount Qty Rank
49 7373194 3 200 180 1
49 106049 2 0 180 2
927 109927 2 0 128 1
927 109946 2 0 25 2
927 109927 3 0 25 3
1096 108891 2 0 120 1
1096 109697 3 0 2500 2
1096 5008960 1 0 250 3
1096 6627903 1 0 120 4

If ProdType=3 and amount not 0 then Rank with Highest amount
If ProdType=3 and amount =0 then ProdType 2 with highest Qty

 

[kjv1611]

xponcall,

What specifically is your issue? An error message? What? If you want an answer, you need to post some details to your situation, not JUST your code.

See here:
faq183-5565
faq183-3179
faq183-874

If you look at the FAQ section for any forum here, you'll find something similar. For this forum, the above are listed under the section, "Effective Forum Participation"

--

"If to err is human, then I must be some kind of human!" -Me

 

[Qik3Coder]

looks like a logic issue, somehow you are losing that row. Which it would be helpful if you highlight the disrepencies using the BOLD[ feature.

You won't be able to have the same row id and id in a real table with a primary key. try constructing a real table with a primary key and see if your statement returns weird data.

-Sometimes the answer to your question is the hack that works

 

[xponcall]

This seem to solved the problem....
thanks

select * from (
select a.*, Rank() over (Partition BY a.rowid order by
case when (a.amount <> 0 and a.prodtype=3)then 0 else 1 end,
case when (a.Qty <> 0 and a.prodtype=2)then 0 else 1 end,
a.Prodtype DESC, a.amount desc , a.qty desc) as rankid
from #table1 a)b
order by rowid,rankid

 

[Qik3Coder]

What fixed the problem?

-Sometimes the answer to your question is the hack that works