SQL Clone
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
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42134 Visits: 20009
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
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 1381
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
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42134 Visits: 20009
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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2465 Visits: 907
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19945 Visits: 7415
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