Top 20 rows - SQL Server Forums

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

Top 20 rows

Author

Topic

crazihouse
Starting Member

4 Posts

Posted - 2011-05-29 : 04:14:40

I have a query that goes as such:

SELECT DISTINCT ProvinceName, CountryName, ProvinceID, CountryID FROM PrintedMapsView WHERE " + sKeywordSearch + " ORDER BY CountryName, ProvinceName

I am using MSSQL 2008 and I have been trying for hours to make it list only the first 20 rows, let alone 21,40 ; 41,60 ; etc.

I tried TOP 20 after SELECT and tried a bunch of tutorials (including at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210 but ended up just failing. :(

What am I missing here? I just want to display the first 20 rows of what is returned.

Many thanks! Any guidance is much appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-29 : 07:37:15

[code]
; with cte as
(
select distinct ProvinceName, CountryName, ProvinceID, CountryID,
row_number over (ORDER BY CountryName, ProvinceName) as row_no
FROM PrintedMapsView
WHERE . . .
)
SELECT *
FROM cte
WHERE row_no between 1 and 20
[/code]

edit : added the distinct

KH
[spoiler]Time is always against us[/spoiler]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-29 : 07:58:18

You need the distinct in the cte in khtan's suggestion.

You were probably getting an error due to putting the top 20 in the wrong position. I usually use a derived table for that - which isn't much different from using a cte but the row_number isn't necessary - but if you want to page then the row_number makes it simpler while we are waiting for the offset feature.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

crazihouse
Starting Member

4 Posts

Posted - 2011-05-29 : 12:45:39

Thank you for the quick responses, khtan and nigelrivett!

It is still not working though. SQL and I will never be friends.

I will admit, I have not been forthcoming. The query isn't as simple as I put it. I just simplified it so it would be pure SQL and no reference to the C# code.

I get the error: Incorrect syntax near the keyword 'OVER'.

Here is what it is (with khtan's code):


string sSQL = "";
sSQL += "; WITH cte AS ";
sSQL += "(";
sSQL += "SELECT DISTINCT ProvinceName_^^ AS ProvinceName, CountryName_^^ AS CountryName, ProvinceID, CountryID, ";
sSQL += "row_number OVER (ORDER BY CountryName_^^, ProvinceName_^^) AS row_no" ;
sSQL += "FROM PrintedMapsView";
string sKeywordSearch += GetKeywordSearchCriteria(sSearch); 
if (sKeywordSearch != "") { sSQL += " WHERE " + sKeywordSearch; }
sSQL += ") ";
sSQL += "SELECT * ";
sSQL += "FROM  cte ";
sSQL += "WHERE row_no BETWEEN 1 AND 20";
sSQL += sSQL.Replace("^^", UserSession.LanguageCode);

Roughly the same thing, right? Where did I go wrong (again..)? Thank you for any help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-29 : 13:15:20

quote:
Originally posted by crazihouse

Thank you for the quick responses, khtan and nigelrivett!

It is still not working though. SQL and I will never be friends.

I will admit, I have not been forthcoming. The query isn't as simple as I put it. I just simplified it so it would be pure SQL and no reference to the C# code.

I get the error: Incorrect syntax near the keyword 'OVER'.

Here is what it is (with khtan's code):

string sSQL = "";
sSQL += "; WITH cte AS ";
sSQL += "(";
sSQL += "SELECT DISTINCT ProvinceName_^^ AS ProvinceName, CountryName_^^ AS CountryName, ProvinceID, CountryID, ";
sSQL += "row_number() OVER (ORDER BY CountryName_^^, ProvinceName_^^) AS row_no" ;
sSQL += "FROM PrintedMapsView";
string sKeywordSearch += GetKeywordSearchCriteria(sSearch);
if (sKeywordSearch != "") { sSQL += " WHERE " + sKeywordSearch; }
sSQL += ") ";
sSQL += "SELECT * ";
sSQL += "FROM cte ";
sSQL += "WHERE row_no BETWEEN 1 AND 20";
sSQL += sSQL.Replace("^^", UserSession.LanguageCode);

Roughly the same thing, right? Where did I go wrong (again..)? Thank you for any help!

No, you're never too old to Yak'n'Roll if you're too young to die.

crazihouse
Starting Member

4 Posts

Posted - 2011-05-29 : 13:47:37

Thank you webfred! Finally no more errors!

With this, the query is returning ALL rows, and now it seems DISTINCT has no more weight, as Provinces are being repeated multiple times, unlike before.

With the original code, this would happen when DISTINCT was removed.

Edit: For the second line, sSQL += "WITH cte AS "; and sSQL += "; WITH cte AS "; make no difference to the query result.


            string sSQL = "";
            sSQL += "WITH cte AS ";
            sSQL += "(";
            sSQL += "SELECT DISTINCT ProvinceName_^^ AS ProvinceName, CountryName_^^ AS CountryName, ProvinceID, CountryID, ";
            sSQL += "row_number() OVER (ORDER BY CountryName_^^, ProvinceName_^^) AS row_no ";
            sSQL += "FROM PrintedMapsView";
            string sKeywordSearch = GetKeywordSearchCriteria(sSearch);
            if (sKeywordSearch != "") { sSQL += " WHERE " + sKeywordSearch; }
            sSQL += ") ";
            sSQL += "SELECT * ";
            sSQL += "FROM  cte ";
            sSQL += "WHERE row_no BETWEEN 1 AND 20";
            sSQL = sSQL.Replace("^^", UserSession.LanguageCode);

crazihouse
Starting Member

4 Posts

Posted - 2011-05-30 : 00:03:01

Figured it out, but did it the hard way. If anyone is curious, I ended up doing it in C# by using MoveTo(row): http://devlibrary.businessobjects.com/businessobjectsxir2/en/en/RAS_SDK/rassdk_java_dg_doc/doc/rassdk_java_dg/Tutorial110.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources