Jump to content

Ordering Last 10 Records Alphabetically


JimHark5

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.