How to return next value when first choice missing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to return next value when first choice missing

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2003-03-29 : 13:42:06

Hi. I have a table that includes the columns below. Customer_ID is self-explanatory. Order_Type can be 2 thru 6. Customers usually have a selection of the mentioned order types, as below:

Customer_ID/ Order_Type
1/ 2
1/ 3
1/ 4
2/ 3
2/ 4
3/ 4
3/ 5

I need to query the table and return all instances where Order_Type = 2 for each customer. However, in the case where a customer does not have Order_Type = 2 (as with customers 2 and 3 above), I need the query to return the next consecutive Order_Type ONLY (only one record per unique customer is returned). The results of the query would look like this:

Customer_ID/ Order_Type
1/ 2
2/ 3
3/ 4

I have been able to execute the above first using VIEWS/JOINS on temp tables, then updating a placeholder column to give the above results. This is quite cumbersome and I wondered if there is a quicker way to simply query the table, as:

Select Customer_ID, Order_Type
from table
where Order_Type = 2
[and if no Order_Type = 2 for each unique Customer_ID, then give me next consecutive order type available]

Any assistance is greatly appreciated.

thx

Edited by - steelkilt on 03/29/2003 13:44:43

Edited by - steelkilt on 03/29/2003 13:45:50

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-29 : 13:59:41

Select Customer_ID, Order_Type
from table t1
where Order_Type = (select min(t2.Order_Type) from table t2 where t2.Customer_ID = t1.Customer_ID and t2.Order_Type >= 2)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-29 : 14:15:21

OR --

SELECT customer_Id, min(Order_Type) as OrderType
FROM
table
WHERE Order_Type >=2

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-29 : 16:00:28

Oops - didn't notice only wanted order type.

but

SELECT customer_Id, min(Order_Type) as OrderType
FROM
table
WHERE Order_Type >=2
group by customer_Id

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources