Jump to content

GROUP BY `column` in Sequence


Manny
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...