StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I am not sure how to do this with My SQL but i am able to build a set of queries in SQL Server 2005 which will provide the intended results. Here is the working sample, its very complex and may be overly complex but that's how i was able to get the desired result:</p> <pre><code>WITH BaseData AS ( SELECT 5 AS ID, '2012-12-10 10:12:37' AS Date UNION ALL SELECT 4 AS ID, '2012-12-10 09:09:55' AS Date UNION ALL SELECT 3 AS ID, '2012-12-09 21:12:35' AS Date UNION ALL SELECT 2 AS ID, '2012-12-09 20:15:07' AS Date UNION ALL SELECT 1 AS ID, '2012-12-09 20:01:42' AS Date ), BaseDataWithRowNum AS ( SELECT ID,DATE, ROW_NUMBER() OVER (ORDER BY Date DESC) AS RowNum FROM BaseData ), InterRelatedDates AS ( SELECT B1.RowNum AS RowNum1,B2.RowNum AS RowNum2 FROM BaseDataWithRowNum B1 INNER JOIN BaseDataWithRowNum B2 ON B1.Date BETWEEN B2.Date AND DATEADD(hh,3,B2.Date) AND B1.RowNum < B2.RowNum AND B1.ID != B2.ID ), InterRelatedDatesWithinMultipleGroups AS ( SELECT G1.RowNum1,G2.RowNum2 FROM InterRelatedDates G1 LEFT JOIN InterRelatedDates G2 ON G1.RowNum2 = G2.RowNum2 AND G1.RowNum1 != G2.RowNum1 ) SELECT BN.ID, BN.Date, CountExcludingOriginalGrouppingRecord +1 AS C FROM ( SELECT RowNum1 AS RowNum,COUNT(1) AS CountExcludingOriginalGrouppingRecord FROM ( -- If a row was used in only one group then it is ok. use as it is SELECT D1.RowNum1 FROM InterRelatedDatesWithinMultipleGroups AS D1 WHERE D1.RowNum2 IS NULL UNION ALL -- In case a row was selected in two groups, choose the one with higher date SELECT Min(D1.RowNum1) FROM InterRelatedDatesWithinMultipleGroups AS D1 WHERE D1.RowNum2 IS NOT NULL GROUP BY D1.RowNum2 ) T GROUP BY RowNum1 ) T2 INNER JOIN BaseDataWithRowNum BN ON BN.RowNum = T2.RowNum </code></pre>

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