Unable to fix the SQL (Data base ranking)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Unable to fix the SQL (Data base ranking)

Author

Topic

sramlaxman
Starting Member

1 Post

Posted - 2011-04-08 : 16:20:41

I am Working with business objects Reporting tool. I have problem understanding the SQL generated by the tool. the SQL is as follows

SELECT
Reporting.dbo.APPLICATION_ID
FROM
Reporting.dbo.INSTANCE
WHERE
Reporting.dbo.APPLICATION_ID IN
(
SELECT
View__1.Column__1
FROM
(
SELECT
Reporting.dbo.INSTANCE.APPLICATION_ID AS Column__1,
RANK() OVER( ORDER BY Reporting.dbo.INSTANCE.TIME_SUM DESC) AS Rk__1
FROM
Reporting.dbo.INSTANCE
) View__1
WHERE View__1.Rk__1 <= 10
)

As specified in last line of the SQL I should get top ten results of the application. I am only getting four applications when I run the SQL against the database and more over the ranking is also not correct with some top applications replaced by lower ones. Please verify the SQL if it is the correct to generate top ten application ID's based on Active time (used in rank function).

Thanks
Ram

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-08 : 16:44:48

[code]-- Original suggestion
SELECT Reporting.dbo.APPLICATION_ID
FROM Reporting.dbo.INSTANCE
WHERE Reporting.dbo.APPLICATION_ID IN (
SELECT View__1.Column__1
FROM (
SELECT Reporting.dbo.INSTANCE.APPLICATION_ID AS Column__1,
RANK() OVER( ORDER BY Reporting.dbo.INSTANCE.TIME_SUM DESC) AS Rk__1
FROM Reporting.dbo.INSTANCE
) View__1
WHERE View__1.Rk__1 <= 10
)

-- Peso
SELECT APPLICATION_ID
FROM (
SELECT APPLICATION_ID,
RANK() OVER (ORDER BY TIME_SUM DESC) AS Rnk
FROM Reporting.dbo.INSTANCE
) AS d
WHERE Rnk <= 10[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources