Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sort issue... table in a proc

Posted on 2013-05-13
9
Medium Priority
?
307 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 41

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

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 49

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 41

Accepted Solution

by:
Sharath earned 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

704 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