Get value with highest ID in group - MySQL

Question

I'm trying to get the row with the highest ID from a group, rather than the lowest. I heard you have to do that JOIN trick, but it's confusing and I can't get my head around it.

My code so far is this:

$userp0l=//userID
$where=//forumID
    SELECT * 
    FROM `noti` 
    WHERE forum='$where' 
    AND user <>  '$userp0l'
    GROUP BY user 
    ORDER BY `id` 
    DESC

It works, however it is showing the first entry in the group, rather than the latest. Anyone know how I can rewrite this?

I just said I'd post all of my code, but looking at it, there's not really much else. I run this statement:

 $elist = mysql_query("SELECT * FROM `noti` WHERE forum='$where' AND user <>  '$userp0l' GROUP BY user ORDER BY `id` DESC") or die(mysql_error());

It gets the information from the database. The information is grouped so I do not capture a user twice when I go to renter data into the table:

if(mysql_num_rows($elist) > 0){
while($elist_result = mysql_fetch_array($elist)){
  $shownotir=$elist_result['shownoti']; 
  $userr=$elist_result['user'];
  $forumr=$elist_result['forum'];
    if($shownotir=="n"){
            $pps="INSERT INTO `noti`(`user`, `forum`, `shownoti`, `forumn`, `madeby`) VALUES ('$userr', '$where', '0', '$forumn', '$uu')";
        mysql_query($pps) or die(mysql_error());
    }
    else {
        $pps="INSERT INTO `noti`(`user`, `forum`, `shownoti`, `forumn`, `madeby`) VALUES ('$userr', '$where', '1', '$forumn', '$uu')";
        mysql_query($pps) or die(mysql_error());
    }
     }
    }

I need to find if the most recent entry of shownoti is a 1 or a 0. The trouble is, when grouping, it takes the first entry rather than the latest. So if the most recent entry has a shownoti value of 0, but the first entry value has a 1, it will always show 1 as it is seeing the first entry. I want to order the groups backwards, basically.

Basically, the answer to this question. I had a little play around with this `JOIN` method, but not managed to achieve anything other than syntax errors http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group — Gaben Newil, Oct 27 '14 at 17:18
what does `JOIN` has to do anything with selecting highest id from a table? — itachi, Oct 27 '14 at 17:21
@GabenNewil Here you have some more examples and ways how to do it: http://dev.mysql.com/doc/refman/5.5/en//example-maximum-column-group-row.html Hope, it helps. — fancyPants, Oct 27 '14 at 17:24
@itachi You can also look at the link I provided in comment above. — fancyPants, Oct 27 '14 at 17:24

Jay Blanchard · Accepted Answer · 2014-10-27T18:48:10.087

0

Use DISTINCT

SELECT DISTINCT `user`, `shownoti`
FROM `noti` 
WHERE forum = '97' 
AND user <> '9797'
GROUP BY `user`, `shownoti`
ORDER BY `id` DESC

You have to remember that 4 (from 420) is greater than 1 (from 1350) so your sorting may have to be a little different. I have created an example here.

edited Oct 27 '14 at 18:48

answered Oct 27 '14 at 17:27

Jay Blanchard

34,243
16
77
119

Just tried and got the same results as though I'd not even used `distinct`. I'm starting to feel as though this is a dead end. I simply just want to get the most recent row of data for each separate `user` value – Gaben Newil Oct 27 '14 at 18:14
I made one more edit to see if you would be closer @GabenNewil. If you could setup a more complete example of your data in SQL Fiddle we could solve the problem. – Jay Blanchard Oct 27 '14 at 18:22
The SQL fiddle is great, aside from the fact I don't get all of the data. I really need `shownoti` and `user`, but `shownoti` is not supposed to be distinct :/ – Gaben Newil Oct 27 '14 at 18:22
Honestly, I feel we may have hit a dead end. There must be countless ways round this, perhaps creating a second table or something – Gaben Newil Oct 27 '14 at 18:36
I don't know what all the data is related to, so telling you how to normalize it would be a chore. Look at this - http://sqlfiddle.com/#!2/19cd7/16 – Jay Blanchard Oct 27 '14 at 18:38
Not a clue what you did, but that final fiddle appears to have fixed my problem. Thank you so much¬ – Gaben Newil Oct 27 '14 at 18:43
It was all in selecting the right things and then performing the right GROUP BY .... glad to have helped. – Jay Blanchard Oct 27 '14 at 18:47

Get value with highest ID in group - MySQL

1 Answers1