June 3, 2015 at 8:45 am
Hi,
I have compiled a report where in SQL query I have used Order By DateEntered, Value DESC but somehow when I check all the lines it does not sort it properly or for example
1022
987
876
870
950
690
580
I have gone through everything but I cannot understand as why is it acting like this?
I have sorted the column sort by value ztoa but still same result.
June 3, 2015 at 8:54 am
what does Order By Value DESC give you?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 3, 2015 at 8:57 am
It gives me values like these
241174
17433
118157
14077
12295
2866
739
June 3, 2015 at 9:04 am
Are these the actual values in the 'DateEntered' column you're attempting to order by?
1022
987
876
870
950
690
580
These arn't date values, they are integers. So, I'm assuming that Jan 1 would be coded as 11 in your column. What you can do is order by an expression that converts DateEntered to a varchar and then left pads with a leading '00' as appropriate.
ORDER BY LEFT('00'+cast(DateEntered as varchar(4)),4);
1022
0987
0950
0876
0870
0690
0580
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 3, 2015 at 9:05 am
J Livingston SQL (6/3/2015)
what does Order By Value DESC give you?
LOL! Havn't had this morning's coffee yet? 😉
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 3, 2015 at 9:08 am
Eric M Russell (6/3/2015)
J Livingston SQL (6/3/2015)
what does Order By Value DESC give you?LOL! Havn't had this morning's coffee yet? 😉
In sql I am using
Order By DateEntered, Value DESC
and in the report I am sorting Value DESC Z to A.
June 3, 2015 at 9:08 am
Eric M Russell (6/3/2015)
J Livingston SQL (6/3/2015)
what does Order By Value DESC give you?LOL! Havn't had this morning's coffee yet? 😉
😛
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 3, 2015 at 9:09 am
yusufm 48726 (6/3/2015)
Hi,I have compiled a report where in SQL query I have used Order By DateEntered, Value DESC but somehow when I check all the lines it does not sort it properly or for example
1022
987
876
870
950
690
580
I have gone through everything but I cannot understand as why is it acting like this?
I have sorted the column sort by value ztoa but still same result.
Can you show us the results of DateEntered and Value side by side as returned by the query, not in separate lists.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 3, 2015 at 9:19 am
Sure I have attached it. This one looks ok but sometimes it gives me wrong value in between.
241,174
17,433
118,157
14,077
12,295
2,866
June 3, 2015 at 9:36 am
yusufm 48726 (6/3/2015)
Sure I have attached it. This one looks ok but sometimes it gives me wrong value in between.
I was assuming that your DateEntered column contained MMDD coded as intergers, but looking more closely at the values I see now that's not the case. I make assumptions too quickly when I havn't finished my own first cup of coffee.
Just to confirm, what is the datatype of DateEntered, and given the original list of values, how do you want them sorted?
1022
987
876
870
950
690
580
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 4, 2015 at 4:14 pm
yusufm 48726 (6/3/2015)
Sure I have attached it. This one looks ok but sometimes it gives me wrong value in between.241,174
17,433
118,157
14,077
12,295
2,866
Based on the attachment, it looks like you're grouping on DateEntered in the report, and Value is the detail rows. Do you have the Sorting for the Row Groups (Details) group set to sort by "Value" Z to A?
Don Simpson
June 5, 2015 at 2:56 am
Yes I have grouped it on date entered and company name and I tried what you suggested and still the same result.
June 5, 2015 at 7:33 am
Keep in mind that your reporting tool can apply it's own sorting regardless of what ORDER BY clause is specified in the SQL query.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 5, 2015 at 10:13 am
yusufm 48726 (6/5/2015)
Yes I have grouped it on date entered and company name and I tried what you suggested and still the same result.
Here is your original post:
yusufm 48726 (6/3/2015)
Hi,I have compiled a report where in SQL query I have used Order By DateEntered, Value DESC but somehow when I check all the lines it does not sort it properly or for example
1022
987
876
870
950
690
580
I have gone through everything but I cannot understand as why is it acting like this?
I have sorted the column sort by value ztoa but still same result.
You are only showing one column when you are sorting/grouping by multiple columns. We need more information to really help.
If you could post the DDL (CREATE TABLE statement) for the table(s) involved, some sample data that represents the problem domain (as INSERT INTO statements) for the table(s) involved, and the expected results based on the sample data we can provide you with much better answers and tested code.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply