Link to home
Start Free TrialLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

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:
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)

Open in new window


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
Avatar of Sharath S
Sharath S
Flag of United States of America image

Avatar of Mike Eghtebas

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
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'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
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect.


Thanks