September 19, 2012 at 3:04 pm
Hi, 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 are
Product | Parts | Status
==================
Laptop1 mouse OK
Laptop1 screen OK
Laptop1 button OK
Laptop2 mouse OK
Laptop2 screen OK
Laptop2 button NOT OK
I would like to a query where it shows the results as below
Product | Overall Status
===================
Laptop1 OK
Laptop2 NOT OK
Appreciate any help and thanks in advance.
September 19, 2012 at 3:07 pm
assuming alphabetical order of the status can be used, this works:
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
Lowell
September 19, 2012 at 3:15 pm
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?
September 19, 2012 at 3:26 pm
kavern (9/19/2012)
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?
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.
select Product, MIN(status)
FROM yourTable
GROUP BY Product
September 19, 2012 at 3:30 pm
iit depends on your data, if any status other than OK, then it fails, for example?
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
Lowell
September 19, 2012 at 11:14 pm
Thank 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.
September 20, 2012 at 12:32 am
Not sure but considering your latest requirements, I think a slight modification to Lowell's original suggestion might perform better:
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
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 2, 2012 at 7:44 am
CREATE 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 FUNCTION
WITH 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=MaxRn
October 2, 2012 at 8:20 am
Change the order of your test data and see of this solution works:
DROP TABLE #TestData
CREATE 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'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2012 at 8:46 am
You are correct. Just wanted to know in one of the queries
select Product, MIN(status)
FROM MyCTE
GROUP BY Product
the 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
October 2, 2012 at 8:51 am
Smash125 (10/2/2012)
You are correct. Just wanted to know in one of the queriesselect Product, MIN(status)
FROM MyCTE
GROUP BY Product
the 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
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'.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2012 at 8:58 am
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=MinRn
ORDER BY Product ASC
October 2, 2012 at 9:10 am
Smash125 (10/2/2012)
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=MinRn
ORDER BY Product ASC
Yes, it's wrong. The partition should be Product, not status:
;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=MinRn
ORDER BY Product ASC, Status
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 3, 2012 at 8:44 am
This 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
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)
...
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.
October 3, 2012 at 12:29 pm
An uncontrolled MIN() could fail as other Statuses appear in the table.
I suggest directly coding for the specific condition you are looking for:
SELECT
Product,
MIN(CASE WHEN Status = 'NOT OK' THEN 'NOT OK' ELSE 'OK' END) AS [Overall Status]
FROM dbo.tablename
GROUP BY
Product
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply