0

Need some stack overflow love. Read the other questions related to group by and sort by and those answers don't seem to work for me. I am not really strong in the finer details of db statements and think there is some dependency or conflict between group and order by statements I am missing.

I have a table (itemOptions) that holds all the options of an item and all the possible values for those options. Think multiple select/drop down boxes for an item and the list of values for each drop down. And unfortunately I can't change the db structure, as a web service is providing the sqlite file.

Here's the structure:

absTtemId    optionName    optionPosition  valueName  valuePosition
item1        size             1             small        1 
item1        size             1             medium       2 
item1        size             1             large        3 
item1        color            2             white        1 
item1        color            2             red          2 
item1        color            2             yellow       3 
item2        name             1             willis       1 
item2        name             1             smith        2 
item2        name             1             bowman       3

The query needs to return optionsNames for a given item ordered by optionPosition, and then a list of valueNames for each option ordered by valueposition, like this

option   valueNames
size     small, medium, large
color    white, red, yellow

I am able to get the grouping of valueNames by option to work, but when I try to add sorting anywhere, sqlite throws errors.

Here's my current sql statement that returns these results, however option and valueNames are never in order.

SELECT   optionName, group_concat(valueName) 
FROM     itemOptions  
WHERE    absItemId = 'item1' 
GROUP BY optionName

option   valueNames
size     medium, small, large
color    yellow, red, white

Here's some of my failed attempts at adding sorting for valueName and optionName

SELECT   optionName, group_concat(valueName ORDER BY valuePosition DESC) 
FROM     itemOptions  
WHERE    absItemId = 'item1' 
GROUP BY optionName

EDIT: sporting for optionName grouping is working now with this. Only valueName sorting within the group_concat not working.

SELECT   optionName, group_concat(valueName) 
FROM     itemOptions  
WHERE    absItemId = 'item1' 
GROUP BY optionName 
ORDER BY optionPosition
nawfal
  • 70,104
  • 56
  • 326
  • 368
Augie
  • 1,341
  • 1
  • 11
  • 18
  • I got the options sorting working with "SELECT optionName, group_concat(valueName) FROM itemOptions WHERE absItemId = 'item1' GROUP BY optionName ORDER BY optionPosition". I was making a use error on this, the only sorting that isn' working now is for valueName – Augie Jul 31 '12 at 16:56
  • maybe you need to do subquery as sqlite may not support `ORDER BY` inside of `GROUP_CONCAT` http://stackoverflow.com/questions/1897352/sqlite-group-concat-ordering – mask8 Jul 31 '12 at 16:59
  • @mask8, I am wondering if ios5 sqlite has something like this, because Mr. Anderson's solution fixed the problem when query is done in firefox sqlite tool, but not within ios5 sqlite query. Looking at the code some more to be certain. thanks. – Augie Jul 31 '12 at 17:19
  • 2
    I just checked official sqlite manual, and it does read `The order of the concatenated elements is arbitrary` so I guess even you sort in the sub-queruy, the order is not guaranteed. you may need to throw multiple queries to accomplish what you're trying – mask8 Jul 31 '12 at 19:02

1 Answers1

3

try

SELECT a.optionName, group_concat(a.valueName) 
FROM (SELECT * FROM itemOptions ORDER BY valuePosition ASC) As a
WHERE a.absItemId = 'item1' 
GROUP BY a.optionName
ORDER BY a.optionName
T I
  • 9,785
  • 4
  • 29
  • 51
  • This got it working when I query within sqlite tool thanks, but not from within iPhone app. I am looking at objC code now to see if code error is culprit and will come back and check this as answer if it is, gave it a 1 up for now. – Augie Jul 31 '12 at 17:17
  • you meant "SELECT a.optionName, group_concat(a.valueName) ..." Right? – Augie Jul 31 '12 at 17:21
  • I think sqlite in ios5 isn't performing group_concat like firefox sqlite tool does because I've logged out the sqlquery and results buffer on iPhone and order isn't being respected, but it is in firefox. I might have to approach this differently and sort on the code side instead of sqlite, but gonna try a few sql statements first. – Augie Jul 31 '12 at 17:43