0

Please login or register to vote for this query.

(click on this box to dismiss)

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)
*/

WITH TOP_TAGS AS (
SELECT TOP 1 P1.TagId AS 'TagId1'
            ,P2.TagId AS 'TagId2'
  FROM PostTags P1
        INNER JOIN PostTags P2 ON P1.PostId = P2.PostId
                              AND P1.TagId < P2.TagId
 GROUP BY P1.TagId, P2.TagId
 ORDER BY Count(*) DESC
)
SELECT T1.TagName AS 'TagName1'
      ,T2.TagName AS 'TagName2'
  FROM TOP_TAGS TT
        INNER JOIN Tags T1 ON T1.Id = TT.TagId1
        INNER JOIN Tags T2 ON T2.Id = TT.TagId2


/*
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;*/

fork query

edited sep 11

created sep 11 Bond