Q&A about the site for operations research and analytics professionals, educators, and students
/* SELECT * FROM PostTypes SELECT P.* FROM Posts P INNER JOIN PostTags PT ON PT.PostId = P.Id INNER JOIN Tags T ON T.Id = PT.TagId WHERE T.TagName IN ('html','css') SELECT PT.* FROM PostTags PT INNER JOIN Tags T ON T.Id = PT.TagId WHERE T.TagName IN ('html','css') --ORDER BY 1,2 SELECT * FROM Tags WHERE TagName IN ('html','css')*/ -- 2 UpMod -- 3 DownMod --SELECT * FROM VoteTypes /* WITH DIS_VOTES AS ( SELECT DISTINCT V.PostID, V.VoteTypeId, Count(*) AS 'SUM' FROM Votes V GROUP BY V.PostID, V.VoteTypeId ) SELECT SUB.* FROM (SELECT DV.PostID ,VT.Name ,DV.SUM ,RANK() OVER(PARTITION BY VoteTypeId ORDER BY DV.SUM DESC) AS VOTE_RANK FROM DIS_VOTES DV INNER JOIN VoteTypes VT ON VT.Id = DV.VoteTypeId WHERE VT.Name IN ('UpMod','DownMod') ) SUB WHERE SUB.VOTE_RANK <= 10 SELECT * FROM POSTS WHERE ID IN (11227902,927386,11227809,927358,2003515,2003505,6591218,179147, 231855,5767357,3296701,153047,4797328,21068763,18018493, 13519990,8333163,10380166,871420,2052396) */ /* WITH ROW_COMMENTS AS ( SELECT C.* ,ROW_NUMBER() OVER(PARTITION BY PostId ORDER BY CreationDate DESC) AS 'RowNum' FROM Comments C WHERE PostId IN (11227902,927386,11227809,927358,2003515, 2003505,6591218,179147,231855,5767357, 3296701,153047,4797328,21068763,18018493, 13519990,8333163,10380166,871420,2052396) ) SELECT RC.PostId ,RC.Text FROM ROW_COMMENTS RC WHERE RC.RowNum = 1*/ /*SELECT C1.PostId ,C1.Text FROM Comments C1 LEFT JOIN Comments C2 ON (C1.PostId = C2.PostId AND C1.id < C2.id) WHERE C2.id IS NULL*/ /*AND C1.PostId IN (11227902,927386,11227809,927358,2003515, 2003505,6591218,179147,231855,5767357, 3296701,153047,4797328,21068763,18018493, 13519990,8333163,10380166,871420,2052396)*/ /*SELECT C1.PostId ,C1.Text FROM Comments C1 LEFT JOIN Comments C2 ON (C1.PostId = C2.PostId AND C1.CreationDate < C2.CreationDate) WHERE C2.CreationDate IS NULL*/ /*AND C1.PostId IN (11227902,927386,11227809,927358,2003515, 2003505,6591218,179147,231855,5767357, 3296701,153047,4797328,21068763,18018493, 13519990,8333163,10380166,871420,2052396)*/ --SELECT C.* -- ,ROW_NUMBER() OVER(PARTITION BY PostId ORDER BY CreationDate DESC) AS 'RowNum' -- FROM Comments C /* SELECT P.PostId, Count(*) AS 'SUM' FROM PostTags P GROUP BY P.PostId ORDER BY Count(*) DESC */ SELECT P1.* FROM PostTags P1 WHERE P1.PostId IN (36361096,17611560,50337611,65228284,50933079, 42069715,76712582,18588252,9588470,35634916, 10998692,67979664,32984317,46847490,36103838, 17361912,37717080,23819273,28997558,57160104) SELECT P1.TagId, P2.TagId, Count(*) FROM PostTags P1 INNER JOIN PostTags P2 ON P1.PostId = P2.PostId AND P1.TagId < P2.TagId WHERE P1.PostId IN (36361096,17611560,50337611,65228284,50933079, 42069715,76712582,18588252,9588470,35634916, 10998692,67979664,32984317,46847490,36103838, 17361912,37717080,23819273,28997558,57160104) GROUP BY P1.TagId, P2.TagId ORDER BY Count(*) DESC /* select a.itemID, b.itemID, COUNT(*) countForCombination from grocery_store a inner join grocery_store b on a.customer_id = b.customer_id and a.itemID < b.itemID group by a.itemID, b.itemID order by countForCombination desc */ /* SELECT TableName = t.name, IndexName = ind.name, IndexId = ind.index_id, ColumnId = ic.index_column_id, ColumnName = col.name, ind.*, ic.*, col.* FROM sys.indexes ind INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id INNER JOIN sys.tables t ON ind.object_id = t.object_id WHERE 1=1 ORDER BY t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal;*/