Sorting unique data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sorting unique data

Author

Topic

mhuynh
Starting Member

4 Posts

Posted - 2010-09-22 : 01:43:59

Hi All,

I have a table with column (varchar) which has data like:

<4 weeks
4 to 8 weeks
8 to 12 weeks
12 to 16 weeks
16 to 20 weeks
20 to 24 weeks
24 to 28 weeks
28 to 32 weeks
44 to 48 weeks
more than 48 weeks

I am having troubles sorting this data from ascending/descending. The data does not sort correctly. It is jumbled up, and inconsistent.

I am using 'ORDER BY LENGTH' is this what I should be doing ?

Devart
Posting Yak Master

102 Posts

Posted - 2010-09-22 : 02:33:00

Hello,

You can try this:

order by cast(left(replace(replace(replace(<YOUR_FIELD_NAME>,' weeks',''),'more than ',''),'<','0 to '),2) as int)

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

Kristen
Test

22859 Posts

Posted - 2010-09-22 : 02:33:58

Are those all the possible values, or can you put anything in there?

If there are fixed values I would put them in a separate table, with an integer ID column, and then store the integer value in the actual column - and then sort by that.

If its freeform you could put them into a new table with an additional "sort order" column. Any new entry (i.e. not found in lookup-table) you sort-last until someone gives it a sort-order value in the linked table.

If its free form, but with structure, then you need to divide it into Start/End weeks and whether it is less than, or greater than, a single value, and then sort on those values.

Just sorting the varchar column isn't going to get you very far unless you parse it, to some extent, first. In order to do a sort you need structured data, what you have got is unstructured data

mhuynh
Starting Member

4 Posts

Posted - 2010-09-22 : 02:57:24

quote:
Originally posted by Devart

Hello,

You can try this:

order by cast(left(replace(replace(replace(<YOUR_FIELD_NAME>,' weeks',''),'more than ',''),'<','0 to '),2) as int)

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

Thank you I will try that.

mhuynh
Starting Member

4 Posts

Posted - 2010-09-22 : 03:00:40

quote:
Originally posted by Kristen

Are those all the possible values, or can you put anything in there?

If there are fixed values I would put them in a separate table, with an integer ID column, and then store the integer value in the actual column - and then sort by that.

If its freeform you could put them into a new table with an additional "sort order" column. Any new entry (i.e. not found in lookup-table) you sort-last until someone gives it a sort-order value in the linked table.

If its free form, but with structure, then you need to divide it into Start/End weeks and whether it is less than, or greater than, a single value, and then sort on those values.

Just sorting the varchar column isn't going to get you very far unless you parse it, to some extent, first. In order to do a sort you need structured data, what you have got is unstructured data

I was afraid that this was the case, I thought there maybe another way around it without re-structuring the data.

Is there another way ?

Yes, these data are fixed values.

Kristen
Test

22859 Posts

Posted - 2010-09-22 : 03:27:55

"these data are fixed values."

Well they ought to be in a lookup table then. otherwise someone, sooner or later, is going to add a new value that doesn't conform to the existing values.

If Devart's suggestion doesn't work you could use a CASE statement:

ORDER BY CASE MyColumnName
WHEN '<4 weeks' THEN 1
WHEN '4 to 8 weeks' THEN 2
...
ELSE 999
END

mhuynh
Starting Member

4 Posts

Posted - 2010-09-22 : 19:25:36

quote:
Originally posted by Kristen

"these data are fixed values."

Well they ought to be in a lookup table then. otherwise someone, sooner or later, is going to add a new value that doesn't conform to the existing values.

If Devart's suggestion doesn't work you could use a CASE statement:

ORDER BY CASE MyColumnName
WHEN '<4 weeks' THEN 1
WHEN '4 to 8 weeks' THEN 2
...
ELSE 999
END

I will try that. Thank both very much for you help and suggestions.

Perfect. That worked really well !!! Thanks again !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources