Solved

Getting a conditional counter into a SQL select statement

Posted on 2010-11-08
2
398 Views
Last Modified: 2012-05-10
I have a select statement that produces these results:

item_no             seq_no            demand                group
123                    5                      P                          0
234                   10                     null                       0
345                   15                     null                       0
456                    20                    P                          0
567                    25                    null                       0

The query currently looks like this:
select             item_no,seq_no,demand,0
from                myTable
order by         seq_no

What I want the statement to do is that whenever it sees a record where demand = "P", increment the group number so that the result set looks like this:

item_no             seq_no            demand                group
123                    5                      P                          10
234                   10                     null                       10
345                   15                     null                       10
456                    20                    P                          20
567                    25                    null                       20

(group does not need to be in increments of 10; it just needs to be unique)

I eventually need to make this into a view.

Can this be done in a view?
0
Comment
Question by:g_johnson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34086640
is seq_no the "sequential" field for the data, aka unique, and the "order by" of your query?if yes:
select t.*
   , (select count(*) from yourtable i where i.seq_no <= t.seq_no and i.demand = 'P' ) group
  from yourtable
order by seq_no

Open in new window

0
 
LVL 4

Author Closing Comment

by:g_johnson
ID: 34086747
Thank you!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question