Jump to content


Photo

Ordering Last 10 Records Alphabetically


  • Please log in to reply
2 replies to this topic

#1 JimHark5

JimHark5
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 07 November 2003 - 09:47 PM

Hi, I\'m wondering if there is a simple way to retrieve the last 10 records from an autonumbered id field in a table, but sort these last 10 records by name, or alphabetically ascending. So if my last records are 500-510, I\'d like just the 10 records in that range ordered alphabetically.

Say I have a simple sql query (I\'m using a mysql database so no TOP 10 queries, just LIMIT) like:

Select id, name
FROM articles

Is there someway that I can use the LIMIT or some other function to do what I want? I\'ve tried using the LIMIT function in various ways, but if I try to ORDER BY name ASC with it like with LIMIT 10, it will give 10 records, but not records 500-510 sorted alphabetically.

I know I could do it either using an additional variable through another recordset or by filtering this query through another recordset, but my page already has enough recordsets, and I\'d like to avoid another if possible.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,015 posts

Posted 08 November 2003 - 08:30 AM

First, you need to know how many records

[php:1:638b3d2544]<?php
$sql = \"SELECT COUNT(*) FROM articles\";
$res = mysql_query($sql);
list($count) = mysql_fetch_row($res);

#want last 10 so
$offset = $count - 10;

#now get them
$sql = \"SELECT id, name FROM articles ORDER BY id LIMIT $offset,10\";
$res = mysql_query($sql);
while (list($id,$name) = mysql_fetch_row($res)) {
$array[$id] = $name;
}
#sort stored recs and echo
asort($array);
foreach($array as $i=>$n) {
echo \"$i $n <br>\";
}
}

?>[/php:1:638b3d2544]

hth
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 PHPcadet

PHPcadet
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationPlant City, Florida

Posted 08 November 2003 - 06:28 PM

To get the last ten records of the table you can change your SELECT to:
[php:1:02bad73fad]<?php
$sql = \"SELECT id, name FROM articles ORDER BY id * -1 LIMIT 0,10\";
?>[/php:1:02bad73fad]
This will eliminate the $offset variable routine. Then you can just store them in the array and sort them as Barand showed.
If builders built buildings the way programmers write programs, then the first termite to come along would destroy civilization -- Anonymous




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users