Alphabetical MYSQL Sort of Last Record In Group

Question

I am trying to follow the answer given in " Retrieving the last record in each group " but I have a problem

My problem is that I am echoing both Y78430's (ie count 1 and 3) when I only want to echo count 3

I am trying to pick the last record of groups of data where the last record is the lower alphabetical letter.

Example of my data here (Table is 'schedulelocation'):-

 Count       cif_train_uid       cif_stp_indicator        Other Data

   1          Y78430                   p                    zzzzzzz
   2          Z45012                   p                    fffffff
   3          Y78430                   o                    sssssss

In the above data there are 2 X Y78430. I would like to echo only one of these. The one with a cif_stp_indicator of o - ie its lower in the alphabet than the 'p'

Here is my code :-

 $b="SELECT s1.cif_train_uid,s1.cif_stp_indicator,s1.schedule_start_date
 FROM schedulelocation s1
 LEFT JOIN schedulelocation s2
  ON (s1.cif_train_uid AND s1.cif_stp_indicator < s2.cif_stp_indicator)
 WHERE s2.cif_stp_indicator is Null AND s1.cif_train_uid='Y78430' ";             

 $l=mysqli_query($mysql_link,$b);   

 if ($l) {

 while($berths=mysqli_fetch_array($l,MYSQLI_ASSOC))
 {  

 echo $berths['cif_train_uid']; 
 echo $berths['cif_stp_indicator'];
 echo $berths['schedule_start_date'];
 echo "</b>";
 echo "</b>";


 }    

             }

Any help greatly appreciated. Thanks

Come on, you can do this! (although I'd suggest that this is in fact the 'first' record in each group - according to your criterion) — Strawberry, Oct 24 '14 at 21:32

score 1 · Accepted Answer · answered Oct 24 '14 at 21:42

You need to use an aggregate query to find the appropriate row to display, and join that to your basic information.

Your aggregate query is this:

              SELECT cif_train_uid, 
                     MIN(cif_stp_indicator) as cif_stp_indicator
                FROM schedulelocation
            GROUP BY cif_train_uid

It returns a list of pairs of (train, indicator) where the indicator is the lowest (lexically first) one for the train.

Then you INNER JOIN it into the rest of your query like this.

     SELECT s1.cif_train_uid,s1.cif_stp_indicator,s1.schedule_start_date
       FROM (   SELECT cif_train_uid, MIN(cif_stp_indicator) as cif_stp_indicator
                  FROM schedulelocation
              GROUP BY cif_train_uid
            ) AS m 
 INNER JOIN schedulelocation s1
         ON m.cif_train_uid=s1.cif_train_uid 
        AND m.cif_stp_indicator = s1.cif_stp_indicator 
  LEFT JOIN schedulelocation s2
         ON s1.cif_train_uid 
        AND s1.cif_stp_indicator < s2.cif_stp_indicator)
      WHERE s2.cif_stp_indicator is Null    /* IS THIS CORRECT ?? */
        AND s1.cif_train_uid='Y78430'

Note I'm not 100% sure what's going on with your self-join operation. I think your first WHERE clause eliminates all the rows where the self-join actually found something. I don't get it.

Thanks Ollie. That works great. I would never have worked all that out. — user2635961, Oct 24 '14 at 22:08

Alphabetical MYSQL Sort of Last Record In Group

1 Answers1