SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Getting the n-th record of a found set
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

barnabeck
Posting Yak Master

Spain
201 Posts

Posted - 09/25/2012 :  08:50:29  Show Profile  Reply with Quote
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 PRODTABLE
I 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

DonAtWork
Flowing Fount of Yak Knowledge

2166 Posts

Posted - 09/25/2012 :  09:07:49  Show Profile  Reply with Quote
Madhi to the rescue:
http://beyondrelational.com/modules/2/blogs/70/posts/10790/find-nth-maximum-value.aspx








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

barnabeck
Posting Yak Master

Spain
201 Posts

Posted - 09/25/2012 :  11:35:15  Show Profile  Reply with Quote
Great!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000