MYSQL - ORDER BY x DESC

[kruser]

Trying to get the last 4 records from a table that have a distinct var1. The following returns the first 4 records in descending order. Any thoughts would be appreciated.

mysql_query("SELECT distinct var1, var2, var3 FROM tablename Where ((var2<1200) | (var2>1299)) ORDER BY var3 DESC LIMIT 4" , $linkID) or die("connection not made");

Best Regards,
Randy
Illinois Real Estate Mobi

[jganders]

too vague. btw, who names their db cols var1, var2, var3??? r ur real col names top secret? are you trying to get the last 4 records in logical order? or in var1 order? then why order by var3?? provide some data or sumfin.

[wige]

I take it you mean the last four based on the column var3?
Stripping down to the most basic version of the query:
SELECT var1, var2 FROM table WHERE (var2 < 1200) ORDER BY var3 DESC LIMIT 4
I swear, I looked at this for about twenty minutes, dug through all the documentation I could find on the MySQL site, did a bunch of Google searches, before saying... duhhh!
SELECT var1, var2 FROM table WHERE (var2 < 1200) ORDER BY var3 ASC LIMIT 4

Limit always starts with the first result, so if you want the last four results, you simply reverse the sort order.

[jganders]

but his cryptic question said (i think) he said the table has a "distinct var1". so i'm thinkin he wants var1 in desc ord. he's gotta weigh in on this. maybe u guessed right wige.

[wige]

From his description, I get the feeling that the query he posted was returning the four records in the correct order, but from the wrong end of the data set. By changing it to my post (from DESC to ASC) he will get the correct pieces of data, but in the reverse order. Most likely, the data is being put into an array in his program, so he would simply need to display or process those array items in reverse order so that he will now have the correct data pieces in the correct order.

for ($i=length($result) -1; $i >= 0; $i--) instead of for ($i= 0; $i < length($result); $i++)

[kruser]

Thanks for all the great input.

The original script worked on one server, but differently on another. I stripped it down a bit and is now working great on both servers! The script is for a real estate site. On the home page it provides the four most recently posted listings, but not duplicate towns so... distict to the rescue

New script - mysql_query("SELECT distinct town_name FROM real_estate_listings ORDER BY table_id DESC LIMIT 4" , $linkID) or die("connection not made b");

When I was first learning mysql and php it was helpful when people used examples calling variables "var1" "var2" "table_name" etc. so when a newbie reads this post, i thought it would be easier.

Best Regards,
Randy

[textypisni32]

Should be ORDER BY var3,var2 DESC LIMIT 4

hope it works
_________________