mysql query the latest date

Posted by user295189 on Stack Overflow See other posts from Stack Overflow or by user295189
Published on 2010-04-29T20:03:01Z Indexed on 2010/04/29 20:07 UTC
Read the original article Hit count: 432

Filed under:

php

|

php5

|

cakephp

|

mysql

|

mysql-query

I am running this query

SEL

ECT
 sh.*,
 u.initials AS initals
FROM
 database1.table1 AS sh
JOIN
 database2.user AS u
ON
 u.userID = sh.userid
WHERE
 id = 123456

AND 
dts = ( SELECT MAX(dts) from database1.table1 )

ORDER BY
 sort_by, category

In the table1 I have records like this

dts                           status                    category         sort_by
2010-04-29 12:20:27        Civil Engineers          Occupation 1 
2010-04-28 12:20:27        Civil Engineers          Occupation 1 
2010-04-28 12:20:54 Married             Marital Status 2      2010-04-28 12:21:15 Smoker             Tobbaco    3
2010-04-27 12:20:27        Civil Engineers          Occupation 1 
2010-04-27 12:20:54 Married             Marital Status 2      2010-04-27 12:21:15 Smoker             Tobbaco    3
2010-04-26 12:20:27        Civil Engineers          Occupation 1 
2010-04-26 12:20:54 Married             Marital Status 2      2010-04-26 12:21:15 Smoker             Tobbaco    3

so if you look at my data, I am choosing the latest entry by category and sort_id. however in some case such as on 29th (2010-04-29 12:20:27) I have only one record. So in this case I want to show occupation for latest and then the rest of them (latest). But currently it displays only one row. Thanks

© Stack Overflow or respective owner

Related posts about php

Magento, NGINX, PHP-FPM, APC, MEMCACHED, 16gb Ram CentOS, Spiking PHP-FPM to 100% CPU

as seen on Server Fault - Search for 'Server Fault'
I have been trying to resolve my issue of spiking cpu caused by php-fpm processes. I've reduced the php-fpm config settings to: pm = ondemand pm.max_children = 12 pm.start_servers = 2 pm.min_spare_servers = 2 pm.max_spare_servers = 10 pm.max_requests = 500 php_admin_value[memory_limit] = 128M Problem… >>> More
PHP Pear Installation on CentOS

as seen on Server Fault - Search for 'Server Fault'
[root@ip ~]# yum install php-pear* Reducing CentOS-5 Testing to included packages only Finished Setting up Install Process Package 1:php-pear-1.8.1-2.el5.centos.noarch already installed and latest versio … >>> More
Apache configurations for php "AddType text/html php" or "AddType application/x-httpd-php php .php"

as seen on Server Fault - Search for 'Server Fault'
I am taking over an application server and discover that it contain the following settings: AddType text/html php Although it works, but my understanding is that it should set as following: AddType application/x-httpd-php php .php What are the key differences between the two settings?… >>> More
mod_rewrite settings causes server to throw HTTP 500 errors instead of 404

as seen on Server Fault - Search for 'Server Fault'
Hello. I have a server with VBulletin forum (working under Apache 2.2, CentOS). The default settings for it in .htaccess are as follows: RewriteEngine on RewriteCond %{HTTP_HOST} ^gsmforum\.ru RewriteRule (.*) http://www.gsmforum.ru/$1 [R=301,L] # If you are having problems or are using VirtualDocumentRoot… >>> More
Problems installing Memcache (PECL extension)

as seen on Server Fault - Search for 'Server Fault'
I have installed memcached fine, and now I will need to install PECL extension memcache. Im running RedHat x86_64 es5. The installation gives me this: downloading memcache-2.2.6.tgz ... Starting to download memcache-2.2.6.tgz (35,957 bytes) ..........done: 35,957 bytes 11 source files, building running:… >>> More

Related posts about php5

Update php 5.2.0 to 5.2.4 with aptitude

as seen on Server Fault - Search for 'Server Fault'
Hi guy, I would like to update my php 5 in my server. At this moment, I use php 5.2.0 so I want to update it to php 5.2.4 (not php 5.3). I tried to do this: aptitude update aptitude upgrade 63 packets were updated but not php which is always in 5.0 How can I update my php please ? Here is the… >>> More
Failed upgrade of PHP on Ubunutu 12.04, error: Sub-process /usr/bin/dpkg returned an error code (1)

as seen on Server Fault - Search for 'Server Fault'
I just tried to upgrade my version of PHP on Ubuntu 12.04 and now I have messed it up. First I did this: sudo add-apt-repository ppa:ondrej/php5-oldstable Then I did this: sudo apt-get update Then finally I did this: sudo apt-get install php5 And now I am getting an error message about Sub-process… >>> More
Adding a CLI for PHP5 on live server

as seen on Server Fault - Search for 'Server Fault'
I want to add command-line support for PHP5 on my server. When I run aptitude install php5-cli I get a message saying that my PHP modules/packages have unmet dependencies. Here is a list of packages that suffer from these "unmet dependencies" and needs and upgrade: php5-gd php5-curl php5-mysql php5-cgi They… >>> More
Update packages on very old ubuntu

as seen on Server Fault - Search for 'Server Fault'
I want to add Mysqli support to a machine running: Server Version: Apache/2.2.4 (Ubuntu) PHP/5.2.3-1ubuntu6.3 I would rather not update more things then I need to. I run the following: sudo apt-get install php5-mysql However, as the ubuntu version is old I get the following. WARNING: The following… >>> More
Downgrade form php5 5.3.10 to php5 5.3.2 in ubuntu 12.04

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
i wanted to install php5 5.3.2, so first deleted all the php5 files sudo apt-get purge php5 php5-cli php5-common php5-mysql and also delete the deb files form /var/cache/apt/archives so now there is no deb file on the system then i add this person repository sudo apt-add-repository ppa:sushkov/personal because… >>> More