Mike Eghtebas
asked on
Sort issue... table in a proc
When I call: select * from tblTarget Order By 1
(see http://sqlfiddle.com/#!3/e9a1d/1)
Note: Table tblSource in this link is different than from the one used to produce the attached image. See T-SQL below for the new table definition.
I get the result in the attached image for:
So, as you can see the results shown in the attached image doesn't sort well. I was thinking if we could add a new sort column called f0 (int type no index) to tblTarget in the abcd stored procedure shown on the first link in this question, then we can have:
tblTarget
==========
f0 f1 f2 f3 ....
--- ----
0 <4>
0 <tbd>
1 A
2 B
.
.
Then we can sort in by:
select * from tblTarget Order By f0
Question: How could this be coded?
Thank you.
tblTarget.png
(see http://sqlfiddle.com/#!3/e9a1d/1)
Note: Table tblSource in this link is different than from the one used to produce the attached image. See T-SQL below for the new table definition.
I get the result in the attached image for:
create TABLE tblSource(
f1 varchar(10)
,f2 varchar(10)
,f3 varchar(10)
,f4 varchar(10)
,f5 varchar(10)
)
create TABLE tbltarget(
f1 varchar(10)
,f2 varchar(10)
,f3 varchar(10)
,f4 varchar(10)
,f5 varchar(10)
)
--select * from tblSource
insert into tblSource (f1, f2, f3, f4, f5)
values('C','R','Q','Z', 'W'),(NULL,'X',NULL,'GZ', 'HW'),('D',NULL,NULL,'CZ', 'MW'),(NULL,'P','P','IZ', 'UW'),(NULL,'UX',NULL,'GZ', NULL)
So, as you can see the results shown in the attached image doesn't sort well. I was thinking if we could add a new sort column called f0 (int type no index) to tblTarget in the abcd stored procedure shown on the first link in this question, then we can have:
tblTarget
==========
f0 f1 f2 f3 ....
--- ----
0 <4>
0 <tbd>
1 A
2 B
.
.
Then we can sort in by:
select * from tblTarget Order By f0
Question: How could this be coded?
Thank you.
tblTarget.png
ASKER
Please see the attached image.
The blank cells marked with red dash should come at the bottom of the columns.
Thanks,
Mike
tblTarget2.png
The blank cells marked with red dash should come at the bottom of the columns.
Thanks,
Mike
tblTarget2.png
ASKER
It seems to me [fo] need to be varchar type. This will be clear after you take a look at the new image attached.
I will post a new question for this because it was not discussed in the original question.
Thanks.
tblTarget3.png
I will post a new question for this because it was not discussed in the original question.
Thanks.
tblTarget3.png
I'd say you are better off staying with an integer column, the latest image you have posted does not indicate a need to varchar in that column to perform the required sort. An integer can be easily incremented without any need for padding etc.
Just explicitly set the sort number you want for each row
nb: I would not have two zeros in that sort column if it can be avoided.
just tweaked the sqlfiddle so you get a unique integer for each row.
http://sqlfiddle.com/#!3/39044/1
Just explicitly set the sort number you want for each row
nb: I would not have two zeros in that sort column if it can be avoided.
just tweaked the sqlfiddle so you get a unique integer for each row.
http://sqlfiddle.com/#!3/39044/1
sorry just noticed you are probably trying to 'push down' items - but still don't think that logic isn't affected by the data type of F0 - which should stay int in my view.
ASKER
Hi PortletPaul,
Thanks for your present and past inputs.
< qty> <3>
<tbd> <tbd>
01 A
02 B
03 C
< qty> will act as caption for that row, a blank after < will keep at the very top
<tbd> likewise to serve ac caption for that row, the text "tbd" will change to something else.
If we are saying there are 3 items and 03 shows 3rd item is C it is kind of nice this way.
I don't expect over 99 items. I understand the ease of incrementing with int but I hope it could be done as shown here.
Thanks for your present and past inputs.
< qty> <3>
<tbd> <tbd>
01 A
02 B
03 C
< qty> will act as caption for that row, a blank after < will keep at the very top
<tbd> likewise to serve ac caption for that row, the text "tbd" will change to something else.
If we are saying there are 3 items and 03 shows 3rd item is C it is kind of nice this way.
I don't expect over 99 items. I understand the ease of incrementing with int but I hope it could be done as shown here.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect.
Thanks
Thanks
ASKER
The new question is at:
https://www.experts-exchange.com/questions/28126947/Change-sort-field-data-type-in-a-proc.html
Thank you.
https://www.experts-exchange.com/questions/28126947/Change-sort-field-data-type-in-a-proc.html
Thank you.
http://sqlfiddle.com/#!3/3710a/1