Geek Answers Handbook

Retrieving Other Field Field Values ​​with MIN / MAX Values

Here is an example of my data and what I would like to see:

JOB OPSEQ OPCOMPLETE OPCODE 100 1 yes pull 100 2 yes weld 100 3 no grind 100 4 no machine 100 5 no asmbl

So, I want to select min (opseq), where opcomplete = no and max (opseq), where opcomplete = yes, as well as op and min opseq opcode. In this example, it will be:
min (opseq): 3
opcode min op: grind
max (opseq): 2 opcode max op: weld

The reason I'm looking for this is to get the operation code of the smallest opseq, which is NOT completed.
I got min and max opseqs to work perfectly. This is what I had:

 (SELECT company, jobnum, MAX(oprseq) AS maxclosed, opcomplete FROM joboper AS joboper_2 WHERE (company = 'lot') AND (opcomplete = '1') GROUP BY company, jobnum, opcomplete) AS t_joboper1 ON joboper.company = t_joboper1.company AND joboper.jobnum = t_joboper1.jobnum INNER JOIN (SELECT company, jobnum, MIN(oprseq) AS minopen, opcomplete FROM joboper AS joboper_1 WHERE (company = 'lot') AND (opcomplete = '0') GROUP BY company, jobnum, opcomplete) AS t_joboper2 ON joboper.company = t_joboper2.company AND joboper.jobnum = t_joboper2.jobnum

So, when I tried to connect my opcodes, this did not work, and I began to enter all kinds of duplicate values. Here is what I wrote:

 (SELECT company, jobnum, MAX(oprseq) AS maxclosed, opcomplete, opcode as maxopcode FROM joboper AS joboper_2 WHERE (company = 'lot') AND (opcomplete = '1') GROUP BY company, jobnum, opcomplete, opcode) AS t_joboper1 ON joboper.company = t_joboper1.company AND joboper.jobnum = t_joboper1.jobnum INNER JOIN (SELECT company, jobnum, MIN(oprseq) AS minopen, opcomplete, opcode as minopcode FROM joboper AS joboper_1 WHERE (company = 'lot') AND (opcomplete = '0') GROUP BY company, jobnum, opcomplete, opcode) AS t_joboper2 ON joboper.company = t_joboper2.company AND joboper.jobnum = t_joboper2.jobnum

Here is my entire code, as it is at the moment (with opcodes, pulling in all duplicate values:

 SELECT jobhead.company, jobhead.jobnum, jobhead.partnum, jobhead.partdescription, jobhead.startdate, jobhead.reqduedate, jobhead.prodqty, jobhead.qtycompleted, joboper.oprseq, joboper.opcode, joboper.opcomplete, joboper.qtycompleted AS joboperqtycomplete, resourcegroup.description AS rgroupdescription, dmrhead.dmrnum, poheader.ponum, vendor.name AS vendorname, t_joboper2.minopen AS minopen, t_joboper2.minopcode AS minopcode, t_joboper1.maxclosed AS maxclosed, t_joboper1.maxopcode AS maxopcode FROM jobhead LEFT OUTER JOIN joboper INNER JOIN (SELECT company, jobnum, MAX(oprseq) AS maxclosed, opcomplete, opcode as maxopcode FROM joboper AS joboper_2 WHERE (company = 'lot') AND (opcomplete = '1') GROUP BY company, jobnum, opcomplete, opcode) AS t_joboper1 ON joboper.company = t_joboper1.company AND joboper.jobnum = t_joboper1.jobnum INNER JOIN (SELECT company, jobnum, MIN(oprseq) AS minopen, opcomplete, opcode as minopcode FROM joboper AS joboper_1 WHERE (company = 'lot') AND (opcomplete = '0') GROUP BY company, jobnum, opcomplete, opcode) AS t_joboper2 ON joboper.company = t_joboper2.company AND joboper.jobnum = t_joboper2.jobnum ON jobhead.company = joboper.company AND jobhead.jobnum = joboper.jobnum LEFT OUTER JOIN resourcegroup ON joboper.company = resourcegroup.company AND joboper.opcode = resourcegroup.opcode LEFT OUTER JOIN dmrhead ON joboper.company = dmrhead.company AND joboper.jobnum = dmrhead.jobnum AND joboper.assemblyseq = dmrhead.assemblyseq AND joboper.oprseq = dmrhead.oprseq LEFT OUTER JOIN porel ON joboper.company = porel.company AND joboper.jobnum = porel.jobnum AND joboper.assemblyseq = porel.assemblyseq AND joboper.oprseq = porel.jobseq LEFT OUTER JOIN podetail ON porel.company = podetail.company AND porel.ponum = podetail.ponum AND porel.poline = podetail.poline LEFT OUTER JOIN poheader ON podetail.company = poheader.company AND podetail.ponum = poheader.ponum LEFT OUTER JOIN vendor ON poheader.company = vendor.company AND poheader.vendornum = vendor.vendornum WHERE (jobhead.jobreleased = 1) AND (jobhead.jobcomplete = 0) AND (jobhead.company = 'lot') AND (jobhead.plant = '001')

I hope all of this makes sense, what I'm trying to do here. If this is not entirely obvious, this is my first question asking a question here. Thank you in advance for your help!!!

New - 12-21-12

Thank you for your help! I tried both of your suggestions, but could not get a single one to get the exact results that I wanted. But each answer helped me get to what it ultimately took to get what I needed. Since this is my first question, I do not know what I should do to mark which answer as a solution? As I said, both answers helped me a lot, and I think that the reason I could not get the results that I wanted from them was my mistake. Having worked on this more, I realized that, although I tried very hard to be clear in my question, as I return, I see how I could formulate things much better. Again, I really appreciate all the help, and I look forward to questions in the future! By the way, this is the code that worked at the end.

  SELECT jobhead.company, jobhead.jobnum, jobhead.partnum, jobhead.partdescription, jobhead.startdate, jobhead.reqduedate, jobhead.prodqty, jobhead.qtycompleted, joboper.oprseq, joboper.opcode, joboper.opcomplete, joboper.qtycompleted AS joboperqtycomplete, resourcegroup.description AS rgroupdescription, dmrhead.dmrnum, poheader.ponum, vendor.name AS vendorname, t_joboper2.minopen, t_joboper1.maxclosed, t_joboper3.opcode AS minopcode FROM jobhead LEFT OUTER JOIN joboper INNER JOIN (SELECT company, jobnum, MAX(oprseq) AS maxclosed, opcomplete FROM joboper AS joboper_1 WHERE (company = 'lot') AND (opcomplete = '1') GROUP BY company, jobnum, opcomplete) AS t_joboper1 ON joboper.company = t_joboper1.company AND joboper.jobnum = t_joboper1.jobnum INNER JOIN (SELECT company, jobnum, MIN(oprseq) AS minopen, opcomplete FROM joboper AS joboper_2 WHERE (company = 'lot') AND (opcomplete = '0') GROUP BY company, jobnum, opcomplete) AS t_joboper2 ON joboper.company = t_joboper2.company AND joboper.jobnum = t_joboper2.jobnum INNER JOIN (SELECT company, jobnum, oprseq, opcomplete, opcode FROM joboper AS joboper_3 WHERE (company = 'lot') AND (opcomplete = '0')) AS t_joboper3 ON t_joboper2.company = t_joboper3.company AND t_joboper2.jobnum = t_joboper3.jobnum AND t_joboper2.minopen = t_joboper3.oprseq ON jobhead.company = joboper.company AND jobhead.jobnum = joboper.jobnum LEFT OUTER JOIN resourcegroup ON joboper.company = resourcegroup.company AND joboper.opcode = resourcegroup.opcode LEFT OUTER JOIN dmrhead ON joboper.company = dmrhead.company AND joboper.jobnum = dmrhead.jobnum AND joboper.assemblyseq = dmrhead.assemblyseq AND joboper.oprseq = dmrhead.oprseq LEFT OUTER JOIN porel ON joboper.company = porel.company AND joboper.jobnum = porel.jobnum AND joboper.assemblyseq = porel.assemblyseq AND joboper.oprseq = porel.jobseq LEFT OUTER JOIN podetail ON porel.company = podetail.company AND porel.ponum = podetail.ponum AND porel.poline = podetail.poline LEFT OUTER JOIN poheader ON podetail.company = poheader.company AND podetail.ponum = poheader.ponum LEFT OUTER JOIN vendor ON poheader.company = vendor.company AND poheader.vendornum = vendor.vendornum WHERE (jobhead.jobreleased = 1) AND (jobhead.jobcomplete = 0) AND (jobhead.company = 'lot') AND (jobhead.plant = '001')
+6
source share
3 answers

Here is one approach:

 select JOB, min(case when OPCOMPLETE = 'no' then OPSEQ end) as MIN_NO_OPSEQ, min(case when OPCOMPLETE = 'no' then OPCODE end) as MIN_NO_OPCODE, min(case when OPCOMPLETE = 'yes' then OPSEQ end) as MAX_YES_OPSEQ, min(case when OPCOMPLETE = 'yes' then OPCODE end) as MAX_YES_OPCODE from ( select JOB, OPSEQ, OPCOMPLETE, OPCODE, rank() over (partition by JOB, OPCOMPLETE order by OPSEQ asc) as R_NO, rank() over (partition by JOB, OPCOMPLETE order by OPSEQ desc) as R_YES from TABLE_NAME ) where OPCOMPLETE = 'no' and R_NO = 1 -- row with min(OPSEQ) where OPCOMPLETE = 'no' or OPCOMPLETE = 'yes' and R_YES = 1 -- row with max(OPSEQ) where OPCOMPLETE = 'yes' group by JOB ;

Notes:

  • not verified.
  • in lines 2 - 5, each min can be changed to max without effect, since only one line will meet all the necessary criteria. min (or max ) is required only because of group by : we combine two lines into one, choosing a nonzero value.
  • for information on rank() , see its documentation on MSDN .
+5
source

I think this is much simpler than you do if OPSEQ is a unique value:

 select opseq, opcode from joboper where opseq in (select min(opseq) from joboper where opcomplete = 'no') or opseq in (select max(opseq) from joboper where opcomplete = 'yes')
+3
source

Assuming you are using SQL Server 2005 or later, you can also use CROSS APPLY , for example:

 SELECT j.JOB, y.OPSEQ AS LastCompleteOPSEQ, y.OPCODE AS LastCompleteOPCODE, n.OPSEQ AS FirstIncompleteOPSEQ, n.OPCODE AS FirstIncompleteOPCODE FROM (SELECT DISTINCT JOB FROM joboper) j CROSS APPLY ( SELECT TOP 1 OPSEQ, OPCODE FROM joboper WHERE JOB = j.JOB AND OPCOMPLETE = 'yes' ORDER BY OPSEQ DESC ) y CROSS APPLY ( SELECT TOP 1 OPSEQ, OPCODE FROM joboper WHERE JOB = j.JOB AND OPCOMPLETE = 'no' ORDER BY OPSEQ ASC ) n ;

You can query in SQL Fiddle .

+1
source

More articles:


All Articles