1

I have a database table with a Value column that contains comma delimited strings and I need to order on the Value column with a SQL query: 

Column ID         Value

  1          ff, yy, bb, ii
  2          kk, aa, ee
  3          dd
  4          cc, zz

If I use a simple query and order by Value ASC, it would result in an order of Column ID: 4, 3, 1, 2, but the desired result is Column ID: 2, 1, 4, 3, since Column ID '2' contains aa, '1' contains bb, and etc.

By the same token, order by Value DESC would result in an order of Column ID: 2, 1, 3, 4, but the desired result is Column ID: 4, 1, 2, 3.

My initial thought is to have two additional columns, 'Lowest Value' and 'Highest Value', and within the query I can order on either 'Lowest Value' and 'Highest value' depending on the sort order. But I am not quite sure how to sort the highest and lowest in each row and insert them into the appropriate columns. Or is there another solution without the use of those two additional columns within the sql statement? I'm not that proficient in sql query, so thanks for your assistance.

Seeker
  • 33
  • 1
  • 4
  • 2
    Poor RDB design leads to difficult problems. Improve your design (as @GolezTrol noted...) – Amit Jun 23 '15 at 17:25
  • Unfortunately, the design is done and I cannot change it. I am only trying to generate reports with existing data. – Seeker Jun 23 '15 at 17:53
  • 1
    Maybe [this](http://sqlperformance.com/2012/07/t-sql-queries/split-strings) will help – Amit Jun 23 '15 at 18:03

2 Answers2

2

Best solution is not to store a single comma separated value at all. Instead have a detail table Values which can have multiple rows per ID, with one value each.

If you have the possibility to alter the data structure (and by your own suggestion of adding columns, it seems you have), I would choose that solution.

To sort by lowest value, you could then write a query similar to the one below. 

select
  t.ID
from
  YourTable t
  left join ValueTable v on v.ID = t.ID
group by
  t.ID
order by
  min(v.Value)

But this structure also allows you to write other, more advanced queries. For instance, this structure makes it easier and more efficient to check if a row matches a specific value, because you don't have to parse the list of values every time, and separate values can be indexed better.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • Can't change the design. I should had been more cleared. I was thinking of creating a view with the two additional columns and then stuff the additional columns in the view. – Seeker Jun 23 '15 at 17:56
  • I'm sorry to hear that. A design should never be written in stone, especially when it's a bad design, but unfortunately companies often have very rigid rules about this. Maybe you can have a look at [this question](http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows). It shows a piece of (complex) SQL to split such values. You could embed that solution in a view. – GolezTrol Jun 23 '15 at 18:06
1

string / array splitting (and creation, for that matter) is covered quite extensively. You might want to have a read of this, one of the best articles out there covering a comparison of the popular methods. Once you have the values the rest is easy.

http://sqlperformance.com/2012/07/t-sql-queries/split-strings

Funnily enough I did something like this just the other week in a cross-applied table function to do some data cleansing, improving performance 8 fold over the looped version in place.

SQLGobbleDeGook
  • 146
  • 7