desc/asc - 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

desc/asc

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2014-03-17 : 15:31:21

Hi,

I have the query returns as the example format below. From the query, I have
WHERE RowNumber BETWEEN 1 AND 100
ORDER BY RowNumber desc

it only returns first 100 records by desc, which are.....,70,99,100. This is not what I want. I want it to return first desc of 100 records which are 104,103,102,102.....
how can I do this? thanks

PageNumber rowNumber
32 104
26 103
15 102
14 101
13 100
12 99
11 70
... ...

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-17 : 15:33:50

Remove the where clause and add a TOP clause as shown below

SELECT TOP (100)
  col1,col2,col3
FROM
  YourTables
ORDER BY
  RowNumber DESC

kt
Yak Posting Veteran

88 Posts

Posted - 2014-03-17 : 15:47:31

I have to use the between since this is passed into the query from the form. From the form, I have the drop down box for user to select how many record they want to display. If they selected 100 then it would be b.w 1-100, but if 150 then b.w 101 -150.....

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-17 : 16:42:41

quote:
Originally posted by kt

I have to use the between since this is passed into the query from the form. From the form, I have the drop down box for user to select how many record they want to display. If they selected 100 then it would be b.w 1-100, but if 150 then b.w 101 -150.....

You can use a variable instead of the constant 100. However, I did not follow the logic that you used to determine that when a 100 is passed in that indicates 1-100 but if a 150 is passed in then that is 101-150. If you can determine the lower and upper bounds based on the number passed in then the query can be modified to accomplish that.

kt
Yak Posting Veteran

88 Posts

Posted - 2014-03-17 : 17:12:57

yes, the lower and upper number can be determined based on what you selected from the form. However, I have the problem with the navigation when try to select top #number#. For example, if I have the search results with total 143 records.

1. If I select 100 records per page from the form, I hit the search button. Page 1: the lower is 1, upper is 100.

2. I hit next to go to page 2 where it have to display the rest of the records which is 43. Page2: lower is 101, upper is 143. It's not right, it suppose show only next 43 records.

At this time, my select top #number# is show top 143 for the page2, it's not right. That's why i have to use between condition
1-100 or 101-143.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-17 : 17:37:55

If you are trying to do pagination, use one of the methods described here depending on your version of SQL Server http://blog.sqlauthority.com/2013/04/14/sql-server-tricks-for-row-offset-and-paging-in-various-versions-of-sql-server/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources