Solved

Getting a conditional counter into a SQL select statement

Posted on 2010-11-08
2
355 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
2 Comments
 
LVL 142

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now