|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Top 2 max operationsdepts per part. IE for 11551 I'd like to return 11551 1730 100 11551 1730 110 etc Then I'd like to build an Exception table (with part and operation and dept) to be able to to override the above and give an operation from the exception table. Once I get started I can run with it. This functionality is key to my application. Thanks Paul OP_PART OPT_DEPT OP_NUMBER --------------- ---------- --------- 11551 8960 080 11551 1730 100 11551 8700 110 11562 1600 010 11621 2800 030 11621 2800 070 11621 2800 090 11621 2800 100 13218 1730 070 13218 1730 070 13218 1730 075 13218 1750 080 13218 1750 090 13218 1750 100 13233 2800 010 13295 1850 050 13295 1850 060 13295 1850 120 13295 1850 150 13295 1850 160 13295 1730 170 13295 1730 180 13295 1750 190 13295 1750 200 13295 1750 210 13295 1750 230 13295 1750 240 13295 1750 450 Paul Ilacqua wrote:
> I would like to query this table below to get the 2 largest op numbers and It seems that your table has duplicates (13218, 1730, 070). Does it> depts per part. IE for 11551 I'd like to return > 11551 1730 100 > 11551 1730 110 etc > > Then I'd like to build an Exception table (with part and operation and dept) > to be able to to override the above and give an operation from the exception > table. Once I get started I can run with it. This functionality is key to > my application. > Thanks > Paul > have a key? Please post DDL so that we don't have to guess. If you really don't have a key then I recommend you clean up and correct your data before you try to do anything more with it. Exactly how do you define "Top 2". Suppose you have 3 or 4 or 5 rows for the same op_part with the same highest op_number? Do you want to treat ties as 1 row or do you want to return more than 2 rows in that case? Here's an example. Lookup the difference between RANK and DENSE_RANK to see which fits your needs. SELECT op_part, opt_dept, op_number FROM (SELECT op_part, opt_dept, op_number, RANK() OVER (PARTITION BY op_part ORDER BY op_number DESC) AS rnk FROM tbl) AS t WHERE rnk <3 ORDER BY op_part, op_number ; You didn't specify what version you are using so I assumed 2005. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- Dave,
Sorry for the shortage of information. There is no key on the table... if there was a dupe it was my typo. I'm using a pk on all three to prevent true dupes. And I am using SQL 2000. There can be dupe op_part & OPT_Dept but not all three. What's your tak on the exception table to "over ride" these values in certain cases? Thanks again OP_PART OPT_DEPT OP_NUMBER --------------- ---------- --------- 11551 8960 080 11551 1730 100 11551 8700 110 11562 1600 010 11621 2800 030 11621 2800 070 11621 2800 090 11621 2800 100 13218 1730 070 13218 1730 075 13218 1750 080 13218 1750 090 13218 1750 100 13233 2800 010 13295 1850 050 13295 1850 060 13295 1850 120 13295 1850 150 13295 1850 160 13295 1730 170 13295 1730 180 13295 1750 190 13295 1750 200 13295 1750 210 13295 1750 230 13295 1750 240 13295 1750 450 Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1157749864.208197.256420@i42g2000cwa.googlegroups.com... > Paul Ilacqua wrote: >> I would like to query this table below to get the 2 largest op numbers >> and >> depts per part. IE for 11551 I'd like to return >> 11551 1730 100 >> 11551 1730 110 etc >> >> Then I'd like to build an Exception table (with part and operation and >> dept) >> to be able to to override the above and give an operation from the >> exception >> table. Once I get started I can run with it. This functionality is key >> to >> my application. >> Thanks >> Paul >> > > It seems that your table has duplicates (13218, 1730, 070). Does it > have a key? Please post DDL so that we don't have to guess. If you > really don't have a key then I recommend you clean up and correct your > data before you try to do anything more with it. > > Exactly how do you define "Top 2". Suppose you have 3 or 4 or 5 rows > for the same op_part with the same highest op_number? Do you want to > treat ties as 1 row or do you want to return more than 2 rows in that > case? Here's an example. Lookup the difference between RANK and > DENSE_RANK to see which fits your needs. > > SELECT op_part, opt_dept, op_number > FROM > (SELECT op_part, opt_dept, op_number, > RANK() OVER (PARTITION BY op_part ORDER BY op_number DESC) AS rnk > FROM tbl) AS t > WHERE rnk <3 > ORDER BY op_part, op_number ; > > You didn't specify what version you are using so I assumed 2005. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > Paul Ilacqua wrote:
> Dave, Try this:> Sorry for the shortage of information. There is no key on the table... if > there was a dupe it was my typo. I'm using a pk on all three to prevent true > dupes. And I am using SQL 2000. There can be dupe op_part & OPT_Dept but not > all three. > What's your tak on the exception table to "over ride" these values in > certain cases? > Thanks again > SELECT t.op_part, t.opt_dept, COALESCE(e.op_number, t.op_number) FROM tbl AS t LEFT JOIN exceptions AS e ON t.op_part = e.op_part AND t.opt_dept = e.opt_dept WHERE t.op_number IN (SELECT DISTINCT TOP 2 op_number FROM tbl WHERE op_part = t.op_part ORDER BY op_number DESC); Again, you may need to adapt this depending on how you wish to treat tied op_number values. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- Dave thanks... I want to understand this. This is my main table... it's
actually about 6 thousand rows of operations. I want to get the MAX(OP) ,dept and OP Description of all part numbers unless there is a matching part number in an exception table, then return the operation FROM the exception table. My exception table is just a part number and operation column with the op being the operation to over ride the current max operation. It will have to be a valid op in order to list all the other data associated with that op (dept, description etc). The exception table OP is a manual entry so I'll validate it on the front end. My initial post was the top 2 ...... but I ran before I could walk. I want to understand this solution before I move on to something more complicated. I appreciate your patience. Paul Part OP Dept OP description 11297 010 2800 ASSEM 11299 010 1600 ASSY PLUG 11337 010 1850 SPIN RIVET GAGE 11337 040 1850 HONE THR 11338 010 1600 ASSY STO 11460 020 2800 MACHINE COMP 11551 080 8960 FIN CUT 11551 100 1730 HEAT TRT 11551 110 8700 FINISH GRIND BORE 11551 120 8710 WASH 11562 010 1600 ASSEMBLE 11621 030 2800 COM REAM 11621 070 2800 DRILL 11621 090 2800 SPOTFACE 11621 100 2800 DRILL 12815 200 2610 ASSEMBLE COMPLETE 12815 200E 2610 WASH 12815 210E 2610 SHIP Show quote > SELECT t.op_part, t.opt_dept, COALESCE(e.op_number, t.op_number) > FROM tbl AS t > LEFT JOIN exceptions AS e > ON t.op_part = e.op_part > AND t.opt_dept = e.opt_dept > WHERE t.op_number IN > (SELECT DISTINCT TOP 2 op_number > FROM tbl > WHERE op_part = t.op_part > ORDER BY op_number DESC); > > Again, you may need to adapt this depending on how you wish to treat > tied op_number values. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > Dave,
Here is a functioning solution to compare to your solution. I'm curious to see how it would compare. If it's close then I've really learned something. SELECT O.[OP_Part] AS PART, O.[OP_Number] AS LAST_OP, O.[OPT_DEPT] AS DEPT, O.[OPT_Des] AS OP_DESCRIPTION FROM Label_Audit..OPS_From_ODF_II O INNER JOIN ( SELECT OD.OP_Part, Coalesce(EXC_OP,MAX(OP_Number)) AS last_op FROM LABEL_AUDIT..OPS_From_ODF_II OD LEFT OUTER JOIN LABEL_AUDIT..OPERATION_EXCEPTIONS e ON OD.OP_Part = e.EXC_PART WHERE OD.OP_Number <> '750' GROUP BY OD.OP_Part, e.EXC_OP ) I ON I.OP_Part = O.OP_Part AND I.last_op = O.OP_Number Show quote "Paul Ilacqua" <pilac***@twcny.rr.com> wrote in message news:e4iGzR40GHA.1252@TK2MSFTNGP04.phx.gbl... >I would like to query this table below to get the 2 largest op numbers and > depts per part. IE for 11551 I'd like to return > 11551 1730 100 > 11551 1730 110 etc > > Then I'd like to build an Exception table (with part and operation and > dept) > to be able to to override the above and give an operation from the > exception > table. Once I get started I can run with it. This functionality is key to > my application. > Thanks > Paul > > OP_PART OPT_DEPT OP_NUMBER > --------------- ---------- --------- > 11551 8960 080 > 11551 1730 100 > 11551 8700 110 > 11562 1600 010 > 11621 2800 030 > 11621 2800 070 > 11621 2800 090 > 11621 2800 100 > 13218 1730 070 > 13218 1730 070 > 13218 1730 075 > 13218 1750 080 > 13218 1750 090 > 13218 1750 100 > 13233 2800 010 > 13295 1850 050 > 13295 1850 060 > 13295 1850 120 > 13295 1850 150 > 13295 1850 160 > 13295 1730 170 > 13295 1730 180 > 13295 1750 190 > 13295 1750 200 > 13295 1750 210 > 13295 1750 230 > 13295 1750 240 > 13295 1750 450 > >  |
|||||||||||||||||||||||
Other interesting topics