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 1WO1201 3 20 20011 0WO1201 2 30 20051 0WO1201 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, ... Prod10from 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 - 2012-09-25 : 08:50:29
