March 7, 2007 at 4:06 pm
i have a query which shows a [data] column which is
in megabytes with values such as: 16568.22 MB
i can't seem to get the ORDER BY clause to ascend or descent
the values properly.
how do i correct that? I just want to the [data] column
to be properly ordered.
ORDER BY is usually pretty simple, but it doesn't seem
to work for values for KB, MB, GB, TB etc.
any ideas?
_________________________
March 7, 2007 at 4:17 pm
You don't give us a sample of the results, but I will bet it IS ordering the values correctly. Your [data] column is most likely a string type of data type (CHAR, VARCHAR). So it will order the values in DICTIONARY order, not NUMERICAL order.
Why are you storing different 'sizes' in the column? If the column is megabytes, then that is what should be stored, not KB, GB, TB, etc. Storing the correct value would let you drop the letter designators from the column and change the data type to NUMERIC(10,2) or whatever you needed.
-SQLBill
March 7, 2007 at 4:18 pm
Your [Data] column must be a varchar? To get it to order correctly, you'll need to strip out the MB value and convert it to a numeric value. For example:
DECLARE @table TABLE (data varchar(100))
INSERT INTO @table
SELECT '16568.22 MB' UNION ALL
SELECT '16568 MB' UNION ALL
SELECT '168.22 MB' UNION ALL
SELECT '568 MB' UNION ALL
SELECT '1658.22 MB' UNION ALL
SELECT '165.22 MB' UNION ALL
SELECT '158 MB'
SELECT *
FROM @table
ORDER BY data
SELECT *
FROM @table
ORDER BY CAST(SUBSTRING(Data,1,CHARINDEX('MB',data) - 2) AS dec) DESC
March 7, 2007 at 4:21 pm
thanks for all the quick replies!
SQLBill
I was just mentioned the kb, mb, gb as a general thought. i'm
not really collecting all that information. just a simple column
with mb.
_________________________
March 7, 2007 at 4:23 pm
john
many thanks.
i'm making those changes now. i'll just change the column
name to something like [mb_size]
_________________________
March 21, 2007 at 10:00 am
If for some reason you needed to keep in varchar (I'm not recommending it), you could do the following:
ORDER BY len([data]),[data]
Steve
March 21, 2007 at 11:55 am
interesting...
i'll keep that in mind.
thanks!
_________________________
March 29, 2007 at 12:25 pm
steve,
ORDER BY len([data]),[data]
why is this not recommended? it's the simplest solution.
_________________________
March 29, 2007 at 12:46 pm
It all depends on the data you are working with. For example, a list of 1, 2, 8, 9, 10, 10a, 20, 89 would be sorted as 1, 2, 8, 9, 10, 20, 89, 10a because 10a is a length of 3.
In your situation of KB, MB, etc., you would have to be sure that there is one and only one space after the number and before the label. Also, if some had commas and others did not, that would mess it up. Then, do you want all of the KB to come before the MB if they are mixed?
Steve
March 29, 2007 at 1:03 pm
actually; you are correct in the list example.
it was not ordered properly, but
when i ran this:
order by len(), size
it was properly ordered.
thanks again.
_________________________
March 29, 2007 at 1:10 pm
incidentally it's for the column of the SYSFILES table.
here's my query:
select name, left (filename, 3) as 'drive', size
from MyDatabase..sysfiles
order by drive, len(), size
_________________________
March 29, 2007 at 1:14 pm
I'm glad it works for you.
Steve
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply