g_johnson
asked on
Getting a conditional counter into a SQL select statement
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER