• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

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
0
Mike Eghtebas
Asked:
Mike Eghtebas
  • 5
  • 2
  • 2
1 Solution
 
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
SharathData EngineerCommented:
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now