Manny Posted November 15, 2015 Share Posted November 15, 2015 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 More sharing options...
Ingolme Posted November 15, 2015 Share Posted November 15, 2015 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 More sharing options...
dsonesuk Posted November 16, 2015 Share Posted November 16, 2015 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now