ranking question

[sonny1974]

i labled my colums 1-7 and rank as my last one.
my ranking isnt working how i want it to
if column 1 is different i want to start the ranking over
so ranking should go 1,2,1,2.
since the first two records are 602137, the ranking should be 1 and then 2

same with 603753


1 2 3 4 5 6 7 RANK

602137 20070216 MECH 0010 9999 5 2 1
602137 20070219 AUDIT 0080 9999 2 1 1
603753 20070119 MECH 0020 9999 46 21 1
603753 20070123 AUDIT 0080 9999 21 9 1

 

[sonny1974]

here is the query i have so far

select wo_nbr,trk.SCH_CMPL_DTE, trk.WORK_CENTER,trk.opn,mach_comp,qty_in,qty_out,COUNT(*) Rank
from trk_table trk

where wo_nbr in ('603753','602137') and qty_in <> qty_out
group by wo_nbr,trk.SCH_CMPL_DTE, trk.WORK_CENTER,trk.opn,mach_comp,qty_in,qty_out

 

[sonny1974]

i think i got it with this query

select trka.wo_nbr,trka.SCH_CMPL_DTE, trka.WORK_CENTER,trka.opn,trka.mach_comp,trka.qty_in,trka.qty_out,COUNT(*) Rank
from trk_table trka
inner join trk_table trkb on trka.wo_nbr =trkb.wo_nbr and trka.opn >= trkb.opn


where trka.wo_nbr in ('603753','602137','602140') and trka.qty_in <> trka.qty_out and trkb.qty_in <> trkb.qty_out
group by trka.wo_nbr,trka.SCH_CMPL_DTE, trka.WORK_CENTER,trka.opn,trka.mach_comp,trka.qty_in,trka.qty_out

 

[taupirho]

Sonny,

Use the same techniques that were shown to you in your very similar question you posted a few days ago

http://www.tek-tips.com/viewthread.cfm?qid=1346108