MySQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMySQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old February 19th, 2017, 05:13 PM
ezenaide's Avatar
ezenaide ezenaide is offline
custom user
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2005
Location: olinda - br
Posts: 78 ezenaide User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 13 h 29 m 52 sec
Reputation Power: 13
Send a message via MSN to ezenaide
'ORDER BY id DESC LIMIT 1' returns nothing

hi everyone.

so i have this blog and i have this really simple script to get articles from the database

Code:
$consulta = mysqli_query($conexao, 'SELECT * FROM post ORDER BY id DESC LIMIT 15');


Code:
	while($registro = mysqli_fetch_assoc($consulta)){
		$sqldate = $registro["data"];
		$date = explode("-",$sqldate);
		$brdate = $date[2]."/".$date[1]."/".$date[0];

		echo '<div class="btitulo">'.$registro["title"].'<span class="bdata">'.$brdate.'</span></div>';
		echo '<div class="btexto">'.$registro["text"].'</div>';


Problem is: last article won't show if i use DESC.
if i just use

Code:
$consulta = mysqli_query($conexao, 'SELECT * FROM post ORDER BY id');


it returns everything ok.
Also noticed that going directly into phpMyAdmin and using the full string it works fine.

Any ideas?
Thx
__________________
Eduardo Zenaide
.
tem a ver?

Reply With Quote
  #2  
Old February 21st, 2017, 04:43 AM
adam8matek adam8matek is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2017
Posts: 1 adam8matek User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 11 m
Reputation Power: 0
There does not seem to be an index on sensor.station_id, which is most probably important here.

There is an actual difference between max() and ORDER BY DESC + LIMIT 1. Many people seem to miss that. NULL values sort first in descending sort order. So ORDER BY timestamp DESC LIMIT 1 returns a row with timestamp IS NULL if it exists, while the aggregate function max() ignores NULL values and returns the latest non-null timestamp.

For your case, since your column d.timestamp is defined NOT NULL (as your update revealed), there is no effective difference. An index with DESC NULLS LAST and the same clause in the ORDER BY for the LIMIT query should still serve you best. I suggest these indexes (my query below builds on the 2nd one):

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMySQL Development > 'ORDER BY id DESC LIMIT 1' returns nothing


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap