gsampath127

gsampath127

SELECT m.* FROM #matches m
INNER JOIN #matches m1 ON m.fromid = m1.toid AND m.toid = m1.fromid AND m1.fromid <=m1.toid
ORDER BY m.toteam

gsampath127

SELECT t.id fromid,t.Team fromteam,t1.id toid,t1.Team toteam
INTO #matches
FROM #Team t
INNER JOIN #Team t1 ON t.id <> t1.id

SELECT * FROM #matches

gsampath127

SELECT * INTO #Team FROM 
(
SELECT 1 AS ID,'India'  Team
UNION ALL
SELECT 2 AS ID,'England'  Team
UNION ALL
SELECT 3 AS ID,'Australia'  Team
UNION ALL
SELECT 4 AS ID,'Newzeland'  Team
UNION ALL

gsampath127

SELECT ProductName,
       TotalAmout,
       sum([TotalAmout]) OVER(ORDER BY ProductName) AS RunningTotal
FROM
  (SELECT prod.ProductName,
          sum(ord.Quantity*ord.UnitPrice) [TotalAmout]
   FROM [Order Details] ord
   INNER JOIN Products prod ON ord.ProductID=prod.ProductID
   GROUP BY prod.ProductName) p

gsampath127

WITH Result As
(
SELECT 1 Num
UNION ALL
SELECT Num+1 FROM Result WHERE Num < 100
)
SELECT * FROM Result

gsampath127

WITH Result As
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY [FirstName] ORDER BY [FirstName] ) 
  as RowNum FROM [dbo].[Employees]
)
SELECT * FROM RESULT where RowNum > 1

gsampath127

WITH Result as(
SELECT *, DENSE_RANK() OVER(ORDER BY Salary DESC) AS drank FROM [dbo].[Employees]
)
SELECT * FROM Result WHERE drank = 3