Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get the overall status of a particular product


Get the overall status of a particular product

Author
Message
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8921 Visits: 19002
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
Smash125
Smash125
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 1377
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8921 Visits: 19002
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
aaron.reese
aaron.reese
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 898
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.
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3934 Visits: 6660
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search