CODEWITHSUNDEEP
phpmysqlselect
user3176950
user3176950

Reputation: 3

PHP / MYSQL - How to select only the most recent value for each different value

i'm french and not really fluent in english, so i'll try to be clear.

i have 'my table' with ID, CODE, value1, value2 and Date

i try to get only most recent Date for each unique CODE, 1 result by CODE

with this request it display nothing:

$sql = 'SELECT value1, value2,Date 
FROM 'my table' 
GROUP BY CODE 
HAVING Date = MAX(Date) 
ORDER BY CODE ASC';

anybody to help me please ?

thank you.

EDIT:

my table is Geoloc

> ID    IMEI    Latitude    Longitude   Date
6   24047780 49.027063  1.147213    2014-01-02 11:19:45
8   24047780 48.738078  1.366940    2014-01-02 11:20:31
9   24048781 48.864715  2.350216    2014-01-02 12:23:12
10  24048781 48.8686132 2.3572024   2014-01-07 16:06:40
11  24048783 49.027063  1.147213    2014-01-07 16:07:45

i want to display one result by imei, the most recent.

EDIT2: im using 

$sql4 = 'SELECT `ID`, `IMEI`, `LATITUDE`, `LONGITUDE`, `DATE`
FROM (SELECT *, 
IF(@lastId = @lastId:=IMEI, @Idx:=@Idx+1, @Idx:=0) rowNumber 
FROM `Geoloc` l, (SELECT @lastId:=0, @Idx:=0) A
ORDER BY `imei` DESC, `date` DESC
) AS A
WHERE rowNumber = 0 ';
// on envoie la requête 
$req4 = mysql_query($sql4) or die('Erreur SQL !<br>'.$sql4.'<br>'.mysql_error());
//the script i use to display informations
while( $Geoloc = mysql_fetch_assoc($req4)) 
            {
            $array[] = "new google.maps.LatLng(".$Geoloc['Latitude']." , ".$Geoloc['Longitude'].") " ;
            $marqueurs[] = 
            "// affichage du marqueur_".$i." - start 
            var marker_".$i."=new google.maps.Marker({position:".$array[$i].",});
            marker_".$i.".setMap(map);
            var infowindow_".$i." = new google.maps.InfoWindow({content:\"".$Geoloc['Date']." - ".$Geoloc['IMEI']." - \"});
            google.maps.event.addListener(marker_".$i.", 'click', function() {infowindow_".$i.".open(map,marker_".$i.");});
    // affichage du marqueur_".$i." - end \n
            ";
            $i++ ;
            }

with this sql request, lattude and longitude not showing, with the sql request gave by aziz, they are displayed, but if 2 max date are the same for 1 imei, it display both

EDIT3: its ok, it was a problem with CAPS, thank you TUN

Upvotes: 0

Views: 174

Answers (4)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try this:

SELECT t.* 
FROM table1 t
JOIN (
  SELECT MAX(`date`) as maxdate, code
  FROM table1
  GROUP BY code
) t1
ON t.code = t1.code AND t.`date` = t1.maxdate
ORDER BY code

For each code, you get the MAX date and then join this inner table with your main table to get required records.

In case you have duplicate pairs of code and maxdate in your data then use the max ID in JOIN as well, like this:

SELECT t.* 
FROM table1 t
JOIN (
  SELECT MAX(`id`) as `id`, MAX(`date`) as maxdate, code
  FROM table1
  GROUP BY code
) t1
ON t.code = t1.code AND t.`date` = t1.maxdate AND t.`id` = t1.`id`
ORDER BY code

Upvotes: 5

Tun Zarni Kyaw
Tun Zarni Kyaw

Reputation: 2119

SELECT `ID`, `IMEI`, `LATITUDE`, `LONGITUDE`, `DATE`
      FROM (SELECT *, 
                   IF(@lastId = @lastId:=IMEI, @Idx:=@Idx+1, @Idx:=0) rowNumber 
            FROM `my table` l, (SELECT @lastId:=0, @Idx:=0) A
            ORDER BY `imei` DESC, `date` DESC
           ) AS A
WHERE rowNumber = 0

Try at SQL Fiddle

Upvotes: 0

Rohit
Rohit

Reputation: 11

Try this query

$sql = 'SELECT value1, value2, MAX(Date) 
FROM `my table` 
GROUP BY 
value1, value2
ORDER BY value1, value2;

Upvotes: 0

Fabien
Fabien

Reputation: 548

this correspond to you want to do ?

$sql = 'SELECT value1, value2,Date
FROM 'my table' 
GROUP BY CODE  
ORDER BY DATE DESC, CODE ASC';

Upvotes: 0

Related Questions