I have this table that describes the state of production of an item on its way through the different workshops: (this example reflects just the records for one ProdID (item) with 4 workshops involved; but there are items that are built in up to 10 workshops.
ProdID Level OPRID Workshop State
----- ----- ----- ------ -----
WO1201 4 10 20012 1
WO1201 3 20 20011 0
WO1201 2 30 20051 0
WO1201 1 40 20031 0
I'm looking for an expression that returns the state of the N-th workshop. The order is described by ascending OPRID (or descending Level)
What I did (-until I found out that OPRID is not always 10,20,30... but can be 15)
Select .., (select Workshop +'('+ State +')'
from PRODROUTE
WHERE PRODROUTE.PRODID =PRODTABLE.PRODID and PRODROUTE.OPRID=10) as Prod1,
(select Workshop +'('+ State +')'
from PRODROUTE
WHERE PRODROUTE.PRODID =PRODTABLE.PRODID and PRODROUTE.OPRID=20) as Prod2,
... Prod10
from PRODTABLEI even found a cool description http://blog.sqlauthority.com/2008/04/02/sql-server-find-nth-highest-salary-of-employee-query-to-retrieve-the-nth-maximum-value/
of how to get the N-th record, ordered by a certain criteria, but it doesn't return NULL in case that you are looking for the 7th Level, while there are only 4 records found.
Martin
Getting the n-th record of a found set
Posted - 09/25/2012 : 08:50:29
