display limited records in descending order

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

SQL Server 2000 Forums

Transact-SQL (2000)

display limited records in descending order

Author

Topic

iamramneek
Starting Member

2 Posts

Posted - 2013-01-15 : 07:57:13

Hi...

I have a table which has a primary key as Record ID. Othere fields include creation date, modification date etc..
I want 2 records per page to be displayed such that the latest record is at the top. Lets take an example.. I have below record ids in table
21
22
23
24
25
26

Now, I want them to be displayed as
On page 1
26
25
On Page 2
24
23
On Page 3
22
21

On this page I have Next and previous Buttons. Next Button is working fine, however the previous button logic is not working.

Please suggest how can I get it work

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-15 : 08:20:34

what do you mean by "it's not working" ? is there an error mesage ? are the parameter values incorrect?
are you manipulating the numbers correctly - so they are correct on the id you pass back to the db?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

iamramneek
Starting Member

2 Posts

Posted - 2013-01-15 : 12:27:55

Hi Jackv ,

Here is the logic I have used...
more.php is the page where I want to display records 2 per page.
FOR displaying NEXT page I created this hyperlink
<a href="more.php?lsthn='.$lessthn.'">Next</a>

where $lessthn = smallest record id I capture on the current page.. eg. for as per my example on earlier post on Page 1 it will be 25, for page 2 it will be 23

NOW on page more.php, NEXT link will work as per below

if (isset($_GET['lsthn']))
{
$lessthan = $_GET['lsthn'];
$sql = mysql_query("SELECT * FROM TABLE where RECORD_ID < $lessthan order by RECORD_CRTN_DT desc limit 2" );
}
In this way 2nd page will display Record number 24 on top and then 23 and so on

FOR displayinng Previous page I created this hyperlink

<a href="more.php?grtrthn='.$grthn.'">Previous</a>

WHERE $grthn = greatest value on current page .. e.g for page 1 it will be 26 , for page 2 it will be 24 and so on

NOW on page more.php, NEXT link will work as per below
if (isset($_GET['grtrthn']))
{
$grtrthan = $_GET['grtrthn'];
$sql = mysql_query("SELECT * FROM TABLE where RECORD_ID > $grtrthan order by RECORD_ID desc limit 2" );
}

This query always returns top two records i.e ist page (record id 26 and record id 25)

Now, If I remove desc and keep it default order, the records order gets changed to Ascending which is not what I want... I always want to display records in the same order as I specified in my previous post.. i.e descending order

I hope you understood my issue..
I you think I have chosen wrong logic... Please can you suggest something else..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources