Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Do a count but dont want it grouped

Status
Not open for further replies.
sonny1974

sonny1974

Technical User
Feb 25, 2007
161
US
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
 
Sort by date Sort by votes
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?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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 .
:)
 
Upvote 0 Downvote
select row_number() over(partition by rcvr order by rcvr)
from rcv_table
 
Upvote 0 Downvote
So, building upon taupirho's excellent solution/technique, your final query (to match your original specifications) would look like this:
Code: 
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

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheLazyPig
  • Question
Button not aligned after click
Replies
0
Views
152
TheLazyPig
TheLazyPig
Chopstik
  • Locked
  • Question
How to identify a package based on a code snippet 1
Replies
1
Views
703
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Get part of a string with SQL 1
Replies
2
Views
851
Andrzejek
Andrzejek
EM1107
  • Locked
  • Question
How to convert CLOB data into number and compare it to other table.
Replies
4
Views
640
carp
carp
Andrzejek
  • Locked
  • Question
Format a number - TO_CHAR() 2
Replies
5
Views
756
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top