Do a count but dont want it grouped

[sonny1974]

here is my query and my results, i added the last column to show what field i wanted. is this possible?

select rcvr,date_rcv,std_prc,location
from rcv_table

RCVR DATE_RCV STD_PRC LOCATION field_ i_want

204275 1/31/2007 0 CSG420 1
204276 1/31/2007 0 1
204276 1/31/2007 0 CSG420 2
204277 1/31/2007 0 1
204277 1/31/2007 0 CSG420 2

 

[SantaMufasa]

Sonny,

Please pardon my density, but I'm not quite sure what you want. How do you want the final results to differ from what you listed, above?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sonny1974]

well the last field isnt there
1
1
2
1
2

should be it ,
since 204275 only shows up once 1 there is a 1

since 204276 shows up twice there should be a 1 then 2

basically i want to do a count , without grouping any of the records

 

[sonny1974]

does that help?

 

[uparna]

Use the RANK or the Row_Number function for this - looks like you dont want count , but want the rank of appearance.

Regards,
S. Jayaram Uparna .

 

[taupirho]

select row_number() over(partition by rcvr order by rcvr)
from rcv_table

 

[SantaMufasa]

So, building upon taupirho's excellent solution/technique, your final query (to match your original specifications) would look like this:

select rcvr
      ,date_rcv
      ,std_prc
      ,location
      ,row_number()
       over(partition by rcvr order by rcvr) field_i_want
  from rcv_table;

  RCVR DATE_RCV      STD_PRC LOCATI FIELD_I_WANT
------ ---------- ---------- ------ ------------
204275 1/31/2007           0 CSG420            1
204276 1/31/2007           0                   1
204276 1/31/2007           0 CSG420            2
204277 1/31/2007           0                   1
204277 1/31/2007           0 CSG420            2

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]