GROUP BY `column` in Sequence

[Manny]

Hi guys. I am trying to populate a <select> list and came up with the idea of using 'optgroup' labels to split options in the list.

 

Some sample data in my database (MySQL) looks like this.

id | league
--------------------
 1 | Championship
 2 | Championship
 3 | Championship
 4 | Premier League
 5 | Division One
 6 | Premiership
 7 | Division One
 8 | Division One
 9 | Premiership
10 | Division One

If I group the results, this is the information I receive.

QUERY
SELECT `league` FROM `table` WHERE `league` IS NOT NULL GROUP BY `league` ORDER BY `id` DESC

RESULT
league
---------------
Championship
Premier League
Division One
Premiership

Unfortunately, this isn't what I want. I know I need something similar to query a sequence of results but I'm unsure how to go about it.

 

In my first code example, I have three 'Championship' values, one 'Premier League', one 'Division One' and so on...

 

What I want is for the results to be grouped (if that is the correct terminology), with another row being returned if it is different from the value of the previous row - regardless of whether it has been used before or not.

 

Here is the data I would expect to be returned.

league
---------------
Championship
Premier League
Division One
Premiership
Division One
Premiership
Division One

As you can see, each individual sequence has been grouped but the results aren't restricted to a DISTINCT value - i.e. there are three 'Division One' results and two 'Premiership'.

 

Is this something MySQL is capable of doing. I've done a search of Google but have so far been unable to find a resolution to my question.

[Ingolme]

This isn't something MySQL can do, since grouping is based on a column independently of the other columns. What you would need to do is get the server-side language to look through the results and construct an array of new results.

[dsonesuk]

This seems to work

"SELECT id, league FROM table c WHERE league NOT IN (SELECT league FROM table WHERE id =(SELECT MAX(id) FROM table WHERE id < c.id)) ORDER BY id DESC";

this is from http://www.sqlines.com/mysql/how-to/select-rows-depending-on-previous-row

Edited November 18, 2015 by dsonesuk