InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 20:07:31 GMT20http://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxAn uncontrolled MIN() could fail as other Statuses appear in the table.I suggest directly coding for the specific condition you are looking for:[code="sql"]SELECT Product, MIN(CASE WHEN Status = 'NOT OK' THEN 'NOT OK' ELSE 'OK' END) AS [Overall Status]FROM dbo.tablenameGROUP BY Product[/code]Wed, 03 Oct 2012 12:29:33 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxThis is an indication that your data is poorly structured.you are using a text field to represent a binary value (Pass/fail)I would suggest the introduction of a lookup table[code="sql"]declare @TestStatus as table(TestStatusDescription nvarchar(50),TestResult int)insert into @testStatus (TestStatusDescription,TestResult) values ('OK',1)insert into @testStatus (TestStatusDescription,TestResult) values ('Pass',1)insert into @testStatus (TestStatusDescription,TestResult) values ('Fail',0)insert into @testStatus (TestStatusDescription,TestResult) values ('Not OK',0)... [/code]one record for every description in your table: you can find these by doing a SELECT DISTINCT on the relevant field.Then join to this @TestStatus table to return 1 or 0 and use the MIN() functionality to determine whether any test has failed.Wed, 03 Oct 2012 08:44:31 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspx[quote][b]Smash125 (10/2/2012)[/b][hr]How about this one correct me if i am wrong;WITH C AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY Status ORDER BY Product) AS Rn FROM #TestData),C1 AS(SELECT *,MIN(Rn)OVER(PARTITION BY Status) MinRn FROM C)SELECT Product,Status FROM C1 WHERE Rn=MinRnORDER BY Product ASC[/quote]Yes, it's wrong. The partition should be Product, not status:[code="sql"];With TestData (Product,Parts,Status) AS ( SELECT 'Laptop1','mouse','OK' UNION ALL SELECT 'Laptop1','screen','OK' UNION ALL SELECT 'Laptop1','button','PASS' UNION ALL SELECT 'Laptop2','mouse','OK' UNION ALL SELECT 'Laptop2','screen','OK' UNION ALL SELECT 'Laptop2','button','NOT OK' UNION ALL SELECT 'Laptop3','button','OK' UNION ALL SELECT 'Laptop3','screen','PASS' UNION ALL SELECT 'Laptop3','mouse','PASS' UNION ALL SELECT 'Laptop4','mouse','PASS' UNION ALL SELECT 'Laptop5','button','PASS' UNION ALL SELECT 'Laptop5','screen','PASS' UNION ALL SELECT 'Laptop5','mouse','PASS' ), C AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY Status ORDER BY Product) AS Rn FROM TestData),C1 AS (SELECT *,MIN(Rn)OVER(PARTITION BY Status) MinRn FROM C)SELECT Product, Status, Rn, MinRn FROM C1 --WHERE Rn=MinRnORDER BY Product ASC, Status[/code]Tue, 02 Oct 2012 09:10:00 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxHow about this one correct me if i am wrong;WITH C AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY Status ORDER BY Product) AS Rn FROM #TestData),C1 AS(SELECT *,MIN(Rn)OVER(PARTITION BY Status) MinRn FROM C)SELECT Product,Status FROM C1 WHERE Rn=MinRnORDER BY Product ASCTue, 02 Oct 2012 08:58:31 GMTSmash125http://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspx[quote][b]Smash125 (10/2/2012)[/b][hr]You are correct. Just wanted to know in one of the queries select Product, MIN(status) FROM MyCTE GROUP BY Productthe second column says MIN(status) just wanted to know which row its directing and why. May i am asking some basic and stupid question but i need to know[/quote]MIN will apply to all rows in the partition (Product) and return the value which is the smallest in the set. In most collations, 'N' is smaller than (will precede) 'O'.Tue, 02 Oct 2012 08:51:19 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxYou are correct. Just wanted to know in one of the queries select Product, MIN(status) FROM MyCTE GROUP BY Productthe second column says MIN(status) just wanted to know which row its directing and why. May i am asking some basic and stupid question but i need to knowTue, 02 Oct 2012 08:46:34 GMTSmash125http://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxChange the order of your test data and see of this solution works:[code="sql"]DROP TABLE #TestDataCREATE TABLE #TestData(Product NVARCHAR(100),Parts NVARCHAR(100),Status NVARCHAR(100))INSERT INTO #TestData(Product,Parts,Status) SELECT 'Laptop1','mouse','OK' UNION ALL SELECT 'Laptop1','screen','OK' UNION ALL SELECT 'Laptop1','button','OK' UNION ALL SELECT 'Laptop2','mouse','OK' UNION ALL SELECT 'Laptop2','button','NOT OK' UNION ALL SELECT 'Laptop2','screen','OK'[/code]Tue, 02 Oct 2012 08:20:39 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxCREATE TABLE #TestData(Product NVARCHAR(100),Parts NVARCHAR(100),Status NVARCHAR(100))INSERT INTO #TestData(Product,Parts,Status) SELECT 'Laptop1','mouse','OK' UNION ALL SELECT 'Laptop1','screen','OK' UNION ALL SELECT 'Laptop1','button','OK' UNION ALL SELECT 'Laptop2','mouse','OK' UNION ALL SELECT 'Laptop2','screen','OK' UNION ALL SELECT 'Laptop2','button','NOT OK'This can be achieved using RANKING FUNCTIONWITH C AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Product) AS Rn FROM #TestData),C1 AS(SELECT *, MAX(Rn) OVER(PARTITION BY Product)AS MaxRn FROM C)SELECT Product,Status FROM C1 WHERE Rn=MaxRnTue, 02 Oct 2012 07:44:09 GMTSmash125http://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxNot sure but considering your latest requirements, I think a slight modification to Lowell's original suggestion might perform better:[code="sql"]With MyCTE (Product,Parts,Status)AS( SELECT 'Laptop1','mouse','OK' UNION ALL SELECT 'Laptop1','screen','OK' UNION ALL SELECT 'Laptop1','button','PASS' UNION ALL SELECT 'Laptop2','mouse','OK' UNION ALL SELECT 'Laptop2','screen','OK' UNION ALL SELECT 'Laptop2','button','NOT OK' UNION ALL SELECT 'Laptop3','button','OK' UNION ALL SELECT 'Laptop3','screen','PASS' UNION ALL SELECT 'Laptop3','mouse','PASS' UNION ALL SELECT 'Laptop4','button','PASS' UNION ALL SELECT 'Laptop4','screen','PASS' UNION ALL SELECT 'Laptop4','mouse','PASS' ) select Product , status=CASE MIN(status) WHEN 'OK' THEN MIN(status) WHEN 'PASS' THEN 'OK' ELSE 'NOT OK' END FROM MyCTE GROUP BY Product[/code]Thu, 20 Sep 2012 00:32:26 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxThank Lowell. I am nearly there. The latest example you gave 'by saying having anything other than OK' gives the status NOT OK.I should have mentioned it earlier. This is because i have another status which is PASS (technician test). OK is another double verification to close the item. (final). PASS + OK = OK PASS + OK + NOT OK = NOT OK. As long as there exist NOT OK in the group of product, overall is NOT OK. I have change the condition in select statement where Status = 'NOT OK' instead of Status <> 'OK'Thanks again.Wed, 19 Sep 2012 23:14:23 GMTkavernhttp://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxiit depends on your data, if any status other than OK, then it fails, for example?[code]With MyCTE (Product,Parts,Status)AS( SELECT 'Laptop1','mouse','OK' UNION ALL SELECT 'Laptop1','screen','OK' UNION ALL SELECT 'Laptop1','button','OK' UNION ALL SELECT 'Laptop2','mouse','OK' UNION ALL SELECT 'Laptop2','screen','OK' UNION ALL SELECT 'Laptop2','button','NOT OK' ) select T1.Product, CASE WHEN T2.Product IS NOT NULL THEN 'Failed' ELSE 'OK' END FROM MyCTE T1 LEFT OUTER JOIN (SELECT Product FROM MyCTE WHere Status <> 'OK') T2 ON T1.Product = T2.Product GROUP BY T1.Product,T2.Product[/code]Wed, 19 Sep 2012 15:30:20 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspx[quote][b]kavern (9/19/2012)[/b][hr]That is assuming i know all the products and i can type one at a time. I was just giving an example on how the table data will look like which will have many data.Is there other query without hard coding the query?[/quote]Lowell's CTE is simply a way for him to provide test data for the actual query. Substitute your actual table for his CTE in the select part of the query.[code="sql"] select Product, MIN(status) FROM yourTable GROUP BY Product[/code]Wed, 19 Sep 2012 15:26:53 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxThat is assuming i know all the products and i can type one at a time. I was just giving an example on how the table data will look like which will have many data.Is there other query without hard coding the query?Wed, 19 Sep 2012 15:15:57 GMTkavernhttp://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxassuming alphabetical order of the status can be used, this works:[code]With MyCTE (Product,Parts,Status)AS( SELECT 'Laptop1','mouse','OK' UNION ALL SELECT 'Laptop1','screen','OK' UNION ALL SELECT 'Laptop1','button','OK' UNION ALL SELECT 'Laptop2','mouse','OK' UNION ALL SELECT 'Laptop2','screen','OK' UNION ALL SELECT 'Laptop2','button','NOT OK' ) select Product, MIN(status) FROM MyCTE GROUP BY Product[/code]Wed, 19 Sep 2012 15:07:17 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1361633-392-1.aspxHi, need some help to write the query as i am not sure how to achieve this.I am having a table for example which consist of items such as laptop.The table columns areProduct | Parts | Status ==================Laptop1 mouse OKLaptop1 screen OKLaptop1 button OKLaptop2 mouse OKLaptop2 screen OKLaptop2 button NOT OKI would like to a query where it shows the results as belowProduct | Overall Status===================Laptop1 OK Laptop2 NOT OKAppreciate any help and thanks in advance.Wed, 19 Sep 2012 15:04:03 GMTkavern