CODEWITHSUNDEEP
sql-serversorting
Seeker
Seeker

Reputation: 33

How to sort comma delimited column in SQL

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.

Upvotes: 1

Views: 3878

Answers (2)

SQLGobbleDeGook
SQLGobbleDeGook

Reputation: 156

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.

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116110

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.

Upvotes: 2

Related Questions