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
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
0
 
SharathData EngineerCommented:
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Please see the attached image.

The blank cells marked with red dash should come at the bottom of the columns.

Thanks,

Mike
tblTarget2.png
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
0
 
PortletPaulfreelancerCommented:
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
0
 
PortletPaulfreelancerCommented:
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.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Perfect.


Thanks
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.