Monday, August 1, 2011

Row_Number without specifying the Column Name



SELECT Row_Number() OVER(ORDER BY(SELECT 0)) As RID,* FROM
 (
 SELECT * FROM (SELECT Top (@N)
Req.[ID],
Req.[RequirementId],
Req.[RequirementNumber],
FROM
[dbo].[Requirements] Req 
INNER JOIN  [dbo].[Projects] p  ON Req.ProjectID = p.ID 
WHERE
Req.ProjectID = @ProjectID
AND ISNUMERIC(Req.RequirementNumber)=1
ORDER BY CONVERT(int,RequirementNumber) DESC) A

UNION ALL


 SELECT * FROM(SELECT Top (@N)
Req.[ID],
Req.[RequirementId],
Req.[RequirementNumber],
FROM
[dbo].[Requirements] Req 
WHERE
Req.ProjectID = @ProjectID
AND ISNUMERIC(Req.RequirementNumber)=0
ORDER BY RequirementNumber DESC ) B
)AB

No comments:

Post a Comment