[RESOLVED] select last 3 records minus 1

dakey

Hello,

i am writing some sql to select the last 3 records but not include the very last one. For example if i have 5 records in my database

1
2
3
4
5

i only want to retrieve records 2, 3 & 4.

can anyone help? below is what i have so far.

SELECT *, date_format(datePosted,'%d.%m.%y') AS uk_date FROM `tableName` WHERE `live` = 1 ORDER BY datePosted DESC LIMIT 3

thanks.

coldwerturkey

until someone wiser than me in sql answers your question, you can 'band-aid' your way around it with php,

ie;

<?php
$array = array (
	'seven', 
	'six', 
	'five', 
	'four' //<< last result - don't want
);

$count = count($array); //or rather mysql_num_rows()

$i = 1;
while ($i != ($count)) { //it will be $i != ($count-1) w/ mysql i think
	$var = $i-1; //<<because (for this example) we're not using mysql_fetch_array()
	echo $array[$var].", ";
	$i++;
}
?>

which outputs;

seven, six, five,

you'll have to change it around to accommodate mysql obviously, but that should help you out a little. my answer really makes no scene to me though as all it's doing is removing the last record which can be done in the query by just making the LIMIT one less...

edit:

i am writing some sql to select the last 3 records but not include the very last one.

if you're selecting three rows and ordering DESC in your query as you have it ie; 3, 2, 1. but you don't want the LAST result; ie; 1, then just select one less?

..I've confused myself. let's use a better rang of numbers: if you have say, 8, 7, 6, 5, 4, 3, 2, 1 and you only want three results, minus the first, ie; 7, 6, 5 then set LIMIT to 4 and with your while() loop just put something like:

$i = 1;
while ($row = mysql_fetch_array($result)) {
	if ($i != 1) echo $row['content']; //<< if it's the first result, don't print
	$i++;
}

bradgrafelman

There's no reason to use PHP to do this; any time you have to manipulate the data extensively using PHP then there is a very high probability that your SQL needs to be optimized to suit your needs.

For example, in this case it's a simple LIMIT/ORDER BY clause:

SELECT foo FROM bar ORDER BY id ASC LIMIT 1, 3

Note that the LIMIT format is correct assuming that you're using MySQL.

coldwerturkey

bradgrafelman;10913731 wrote:
There's no reason to use PHP to do this; any time you have to manipulate the data extensively using PHP then there is a very high probability that your SQL needs to be optimized to suit your needs.

For example, in this case it's a simple LIMIT/ORDER BY clause:
SELECT foo FROM bar ORDER BY id ASC LIMIT 1, 3
Note that the LIMIT format is correct assuming that you're using MySQL.

fml, I can't believe I forgot about that.

bpat1434

If you want the last 3, you'd want to sort DESC instead of ASC 😉

bradgrafelman

bpat1434;10913752 wrote:
If you want the last 3, you'd want to sort DESC instead of ASC 😉

That's true; I just based my model off of what the OP referred to as "last."

Weedpacket

bradgrafelman wrote:
That's true; I just based my model off of what the OP referred to as "last."

It would have been less ambiguous if there had been six records to select from instead of five....

bradgrafelman

Er... I feel dumb now. You're right - it should be DESC instead of ASC, assuming "last" does indeed mean the latest entries. :o