Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Getting a conditional counter into a SQL select statement

Posted on 2010-11-08
2
Medium Priority
?
417 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

610 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