Solved

Sort issue... table in a proc

Posted on 2013-05-13
9
304 Views
Last Modified: 2013-05-13
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
Comment
Question by:Mike Eghtebas
  • 5
  • 2
  • 2
9 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39163063
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39163158
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39163167
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39163263
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39163286
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39163315
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
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39163339
0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 39163404
Perfect.


Thanks
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 39163428
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question