Displaying the latest data in SQL

Question

Can anyone please help me? I'm a newbie on PHP so please understand. Here's my code.

$query="SELECT MAX(mid) as mid, ticketno FROM tbl_message GROUP BY ticketno";
$result=mysql_query($query);
while($row = mysql_fetch_array($result)){ 
echo $row['mid'];
echo $row['ticketno'];
}

DISPLAYS THIS

 - mid    ticketno
 - 2-------21510
 - 1-------24693
 - 4-------24693

WHAT I WANT

 - mid    ticketno
 - 2-------21510
 - 4-------24693

My database :

enter image description here

Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 04 '15 at 13:07
Does your query, with GROUP BY, really return ticketno 24693 twice?!? — jarlh, May 04 '15 at 13:14
@Jay thank you for informing me. I will on my next project. I dont want to re code again — shin, May 04 '15 at 13:15
how does the program know you don't want `1-------24693`? what is that logic? — KM., May 04 '15 at 13:27

score 5 · Answer 1 · answered May 04 '15 at 13:08

5

Use ORDER BY . Try with -

$query="SELECT mid, ticketno FROM tbl_message GROUP BY ticketno ORDER BY mid DESC";

answered May 04 '15 at 13:08

Sougata Bose

31,517
8
49
87

1

i dont know why i doesnt work on me. it returns 3-24693||2-21510||1-24693 – shin May 04 '15 at 13:21

score 1 · Answer 2 · answered May 04 '15 at 13:55

1

SELECT t.*
FROM tbl_message t
WHERE NOT EXISTS ( SELECT 'a'
                   FROM tbl_message t2
                   WHERE t2.ticketno = t.ticketno
                   AND t2.mid > t.mid
                  )

answered May 04 '15 at 13:55

Javaluca

859
1
6
13

score 0 · Accepted Answer · answered May 04 '15 at 13:31

Okay so basically I just changed varchar to int (type of TICKETNO) then it returns the most latest ticketno.

I used this query

$query="SELECT MAX(mid) as mid, ticketno FROM tbl_message GROUP BY ticketno";

i used MAX() to return only the latest ticket no and GROUP BY ticketno so that it would return the other unique ticket no.

I guess varchar reads spaces.

Displaying the latest data in SQL

3 Answers3