StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Multiple occurences would be the easiest. You count how many of each combination you can find and select those with a count bigger than 3.</p> <pre><code>SELECT S.Machine, S.Station, S.DefectCode, S.CreateTime FROM ( SELECT Machine, Station, DefectCode, CreateTime, COUNT(CreateTime) OVER (PARTITION BY Machine, Station, DefectCode) AS DefectCount FROM SAMPLE WHERE Machine = :machine ) S WHERE S.DefectCount > 3 </code></pre> <p>Consecutive occurence is a bit harder (the following query may not be the most optimal one). Assuming consecutive is 'consecutive date', you could try the following:</p> <pre><code>WITH OrderedSample AS ( SELECT Machine, Station, DefectCode, CreateTime, ROW_NUMBER() OVER (ORDER BY CreateTime) AS RowNr FROM SAMPLE WHERE Machine = :machine ) SELECT Current.Machine, Current.Station, Current.DefectCode, Current.CreateTime FROM OrderedSample Current WHERE EXISTS ( SELECT 1 FROM OrderedSample Next WHERE Current.Machine = Next.Machine AND Current.Station = Next.Station AND Current.DefectCode = Next.DefectCode AND Current.RowNr = Next.RowNr - 1 ) OR EXISTS ( SELECT 1 FROM OrderedSample Prev WHERE Current.Machine = Prev.Machine AND Current.Station = Prev.Station AND Current.DefectCode = Prev.DefectCode AND Current.RowNr = Prev.RowNr + 1 ) </code></pre> <p><strong>EDIT</strong></p> <p>Just realized the second query becomes a bit easier because you are working with a fixed Machine:</p> <pre><code>WITH OrderedSample AS ( SELECT Machine, Station, DefectCode, CreateTime, ROW_NUMBER() OVER (ORDER BY CreateTime) AS RowNr FROM SAMPLE WHERE Machine = :machine ) SELECT Curr.Machine, Curr.Station, Curr.DefectCode, Curr.CreateTime, RowNr FROM OrderedSample Curr WHERE EXISTS ( SELECT 1 FROM OrderedSample Next WHERE Curr.Station = Next.Station AND Curr.DefectCode = Next.DefectCode AND Curr.RowNr = Next.RowNr - 1 ) OR EXISTS ( SELECT 1 FROM OrderedSample Prev WHERE Curr.Station = Prev.Station AND Curr.DefectCode = Prev.DefectCode AND Curr.RowNr = Prev.RowNr + 1 ) </code></pre> <p><strong>EDIT 2</strong>: Just noticed Machine & Station are two columns, edited</p> <p><strong>EDIT 3</strong>: Some fixes based on <a href="http://www.sqlfiddle.com/#!3/50b92/31" rel="nofollow">Sql Fiddle example</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload