Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Get the overall status of a particular product Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 8:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 7,128, Visits: 13,509
Smash125 (10/2/2012)
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


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'.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1367092
Posted Tuesday, October 2, 2012 8:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 194, Visits: 1,088
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
Post #1367098
Posted Tuesday, October 2, 2012 9:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 7,128, Visits: 13,509
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1367109
Posted Wednesday, October 3, 2012 8:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:48 AM
Points: 386, Visits: 623
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.

Post #1367739
Posted Wednesday, October 3, 2012 12:29 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 1,970, Visits: 2,914
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1367954
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse