Sorting problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sorting problem

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-04 : 08:30:24

Hi, I have sorting problem in sql for questions.

I have questions list in table and when i sort that column it returns like below:

A
B
Q1. What is your current job title?
Q10. What is your current job title?
Q10. Overall Experience With Alliant Insurance Services
Q2. What is your current job title test test test?
W

OUT PUT.
A
B
Q1. What is your current job title?
Q2. What is your current job title test test test?
Q10. What is your current job title?
Q10. Overall Experience With Alliant Insurance Services
W

Please help : )

Sachin.Nand

2937 Posts

Posted - 2010-08-04 : 08:42:57

[code]
declare @tbl as table(val varchar(40))
insert into @tbl
select 'A' union all
select 'B' union all
select 'Q1' union all
select 'Q10' union all
select 'Q10'union all
select 'Q2'union all
select 'W'

select * from @tbl
order by ASCII(val),LEN(val)
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-04 : 08:59:56

If i add Q3

declare @tbl as table(val varchar(40))
insert into @tbl
select 'A' union all
select 'B' union all
select 'Q1' union all
select 'Q10' union all
select 'Q10'union all
select 'Q3'union all
select 'Q2'union all
select 'W'

select * from @tbl
order by ASCII(val),LEN(val)

It will not work :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 09:18:05

select * from @tbl
order by LEN(val), val

N 56°04'39.26"
E 12°55'05.63"

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-04 : 09:43:44

Peso... thank you for your answer but i want Alpha and then numeric
in your query
your result come as:
A
B
W
Q1
Q2
Q3
Q9
Q10
Q10

I want it like:
OUTPUT
---------
A
B
Q1
Q2
Q3
Q9
Q10
Q10
W

So any number of string characters with alpha and then numeric can be handled

Please help..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 09:50:05

order by LEFT(VAL, 1), LEN(val), val

N 56°04'39.26"
E 12°55'05.63"

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-04 : 09:54:20

Now working with below one.. Plz

declare @tbl as table(val varchar(40))
insert into @tbl
select 'G' union all
select 'AM' union all
select 'CS' union all
select 'VG' union all
select 'WS' union all
select 'CEO' union all
select 'ban1' union all
select 'ban2' union all
select 'Fair' union all
select 'Poor' union all
select 'RAMM' union all
select 'SREP' union all
select 'RSSEP' union all
select 'Region' union all
select 'CEIOOOO' union all
select 'Banner 1' union all
select 'Job Title' union all
select 'Sales Office' union all
select 'Sequential serial number' union all
select 'Q2. Relationship With Aon' union all
select 'Q2. Relationship With Hewitt' union all
select 'Q2. Relationship With Mercer' union all
select 'Q2. Relationship With Willis' union all
select 'Q2. Relationship With Lockton' union all
select 'Q9. Future Opportunity With Aon' union all
select 'Q10. Overall Experience With Aon' union all
select 'Q2. Relationship With Gallagher'

select *, len(val) from @tbl
order by LEFT(VAL, 1), LEN(val), val

Not working

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 10:02:46

Are there more sample data of which we know nothing?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 10:08:22

[code]declare @tbl as table(val varchar(40))
insert into @tbl
select 'G' union all
select 'AM' union all
select 'CS' union all
select 'VG' union all
select 'WS' union all
select 'CEO' union all
select 'ban1' union all
select 'ban2' union all
select 'Fair' union all
select 'Poor' union all
select 'RAMM' union all
select 'SREP' union all
select 'RSSEP' union all
select 'Region' union all
select 'CEIOOOO' union all
select 'Banner 1' union all
select 'Job Title' union all
select 'Sales Office' union all
select 'Sequential serial number' union all
select 'Q2. Relationship With Aon' union all
select 'Q2. Relationship With Hewitt' union all
select 'Q2. Relationship With Mercer' union all
select 'Q2. Relationship With Willis' union all
select 'Q2. Relationship With Lockton' union all
select 'Q9. Future Opportunity With Aon' union all
select 'Q10. Overall Experience With Aon' union all
select 'Q2. Relationship With Gallagher'

SELECT *
FROM @tbl
ORDER BY LEFT(val, 1),
CASE PATINDEX('Q[0-9]%', val)
WHEN 0 THEN LEN(SUBSTRING(val, 2, charindex(val, '.')))
ELSE LEN(val)
END,
CASE PATINDEX('Q[0-9]%', val)
WHEN 0 THEN SUBSTRING(val, 2, charindex(val, '.'))
ELSE val
END[/code]

N 56°04'39.26"
E 12°55'05.63"

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-04 : 10:08:38

No.. its final sample data Just instead of Q1 it may be A1 like that..

declare @tbl as table(val varchar(40))
insert into @tbl
select 'G' union all
select 'AM' union all
select 'CS' union all
select 'VG' union all
select 'WS' union all
select 'CEO' union all
select 'ban1' union all
select 'ban2' union all
select 'Fair' union all
select 'Poor' union all
select 'RAMM' union all
select 'SREP' union all
select 'RSSEP' union all
select 'Region' union all
select 'CEIOOOO' union all
select 'Banner 1' union all
select 'Job Title' union all
select 'Sales Office' union all
select 'Sequential serial number' union all
select 'Q2. Relationship With Aon' union all
select 'Q2. Relationship With Hewitt' union all
select 'Q2. Relationship With Mercer' union all
select 'Q2. Relationship With Willis' union all
select 'Q2. Relationship With Lockton' union all
select 'Q9. Future Opportunity With Aon' union all
select 'Q10. Overall Experience With Aon' union all
select 'Q2. Relationship With Gallagher'

select *, len(val) from @tbl
order by LEFT(VAL, 1), LEN(val), val

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 10:11:50

[code]SELECT *
FROM @tbl
ORDER BY LEFT(val, 1),
CASE PATINDEX('[A-Z][0-9]%', val)
WHEN 0 THEN LEN(SUBSTRING(val, 2, charindex(val, '.')))
ELSE LEN(val)
END,
CASE PATINDEX('[A-Z][0-9]%', val)
WHEN 0 THEN SUBSTRING(val, 2, charindex(val, '.'))
ELSE val
END[/code]

N 56°04'39.26"
E 12°55'05.63"

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-04 : 10:21:56

Thanks but its not working with Q2 data..
And one more thing is can we have generic solution like Question dont have dot (.) in line then...also it will work with data..
for eg, Q2. test data -- it may be like
Q2 test data -- it may be like
see

declare @tbl as table(val varchar(40))
insert into @tbl
select 'G' union all
select 'AM' union all
select 'CS' union all
select 'VG' union all
select 'WS' union all
select 'CEO' union all
select 'ban1' union all
select 'ban2' union all
select 'Fair' union all
select 'Poor' union all
select 'RAMM' union all
select 'SREP' union all
select 'RSSEP' union all
select 'Region' union all
select 'CEIOOOO' union all
select 'Banner 1' union all
select 'Job Title' union all
select 'Sales Office' union all
select 'Sequential serial number' union all
select 'Q2. Relationship With Aon' union all
select 'Q2. Relationship With Hewitt' union all
select 'Q2. Relationship With Mercer' union all
select 'Q2. Relationship With Willis' union all
select 'Q2. Relationship With Lockton' union all
select 'Q9. Future Opportunity With Aon' union all
select 'Q10. Overall Experience With Aon' union all
select 'Q2. Relationship With Gallagher'

--select *, len(val) from @tbl
--order by LEFT(VAL, 1), LEN(val), val

SELECT *
FROM @tbl
ORDER BY LEFT(val, 1),
CASE PATINDEX('[A-Z][0-9]%', val)
WHEN 0 THEN LEN(SUBSTRING(val, 2, charindex(val, '.')))
ELSE LEN(val)
END,
CASE PATINDEX('[A-Z][0-9]%', val)
WHEN 0 THEN SUBSTRING(val, 2, charindex(val, '.'))
ELSE val
END

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-04 : 10:51:15

anyone can please help on this type of alpha numeric sorting

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 11:06:57

This is the result I get. What is wrong?

val
AM
ban1
ban2
Banner 1
CEO
CS
CEIOOOO
Fair
G
Job Title
Poor
Q2. Relationship With Aon
Q2. Relationship With Hewitt
Q2. Relationship With Mercer
Q2. Relationship With Willis
Q2. Relationship With Lockton
Q2. Relationship With Gallagher
Q9. Future Opportunity With Aon
Q10. Overall Experience With Aon
RAMM
RSSEP
Region
Sales Office
Sequential serial number
SREP
VG
WS

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 11:08:28

Replace "charindex(val, '.')" with "charindex(val, ' ')" if you not always have "." as separator.
And again, why haven't you posted the full and complete sample set?
Do you, or we, gain from revealing little by little?

N 56°04'39.26"
E 12°55'05.63"

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-04 : 11:11:14

IF YOU ALTER STRING OF 'Q2. Relationship With Gallagher' TO 'Q2. Relationship With Gallagher kkkkkk' IT WILL NOT WORK..

quote:

Originally posted by Peso

This is the result I get. What is wrong?

val
AM
ban1
ban2
Banner 1
CEO
CS
CEIOOOO
Fair
G
Job Title
Poor
Q2. Relationship With Aon
Q2. Relationship With Hewitt
Q2. Relationship With Mercer
Q2. Relationship With Willis
Q2. Relationship With Lockton
Q2. Relationship With Gallagher
Q9. Future Opportunity With Aon
Q10. Overall Experience With Aon
RAMM
RSSEP
Region
Sales Office
Sequential serial number
SREP
VG
WS

N 56°04'39.26"
E 12°55'05.63"

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-04 : 11:21:43

Why you checked with lenth of character it can be anything..

see below one

declare @tbl as table(val varchar(40))
insert into @tbl
select 'G' union all
select 'AM' union all
select 'CS' union all
select 'VG' union all
select 'WS' union all
select 'CEO' union all
select 'ban1' union all
select 'ban2' union all
select 'Fair' union all
select 'Poor' union all
select 'RAMM' union all
select 'SREP' union all
select 'RSSEP' union all
select 'Region' union all
select 'CEIOOOO' union all
select 'Banner 1' union all
select 'Job Title' union all
select 'Sales Office' union all
select 'Sequential serial number' union all
select 'Q10. Overall Experience With Aon' union all
select 'Q2. Relationship With Aon' union all
select 'Q2. Relationship With Hewitt' union all
select 'Q2. Relationship With Mercer' union all
select 'Q2. Relationship With Willis' union all
select 'Q2. Relationship With Lockton' union all
select 'Q9. Future Opportunity With Aon' union all
select 'Q2. Relationship With Gallagher kleyoie'

SELECT *, LEN(SUBSTRING(val, 2, charindex(val, '.'))), SUBSTRING(val, 2, charindex(val, '.')),
LEFT(val, 1), len(val)
FROM @tbl
ORDER BY LEFT(val, 1),
--CASE PATINDEX('[A-Z][0-9]%', val)
--WHEN 0 THEN LEN(SUBSTRING(val, 2, charindex(val, '.')))
--ELSE
LEN(val),
--END,
--CASE PATINDEX('[A-Z][0-9]%', val)
--WHEN 0 THEN SUBSTRING(val, 2, charindex(val, '.'))
--ELSE
val
--END

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-04 : 11:24:07

This is final complete set..of result string...
just problem of lenth of string coming..

quote:
Originally posted by Peso

Replace "charindex(val, '.')" with "charindex(val, ' ')" if you not always have "." as separator.
And again, why haven't you posted the full and complete sample set?
Do you, or we, gain from revealing little by little?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 11:29:47

Now, this should give you an idea to continue if the output doesn't match your preference (of which I still don't know).

declare @tbl as table(val varchar(40))
insert into @tbl
select 'G' union all
select 'AM' union all
select 'CS' union all
select 'VG' union all
select 'WS' union all
select 'CEO' union all
select 'ban1' union all
select 'ban2' union all
select 'Fair' union all
select 'Poor' union all
select 'RAMM' union all
select 'SREP' union all
select 'RSSEP' union all
select 'Region' union all
select 'CEIOOOO' union all
select 'Banner 1' union all
select 'Job Title' union all
select 'Sales Office' union all
select 'Sequential serial number' union all
select 'Q2. Relationship With Aon' union all
select 'Q2. Relationship With Hewitt' union all
select 'Q2. Relationship With Mercer' union all
select 'Q2. Relationship With Willis' union all
select 'Q2. Relationship With Lockton' union all
select 'Q9. Future Opportunity With Aon' union all
select 'Q10. Overall Experience With Aon' union all
select 'Q2. Relationship With Gallagher kkkkkk' 

--select *, len(val) from @tbl
--order by LEFT(VAL, 1), LEN(val), val

SELECT		*,
		PATINDEX('[A-Z][0-9]%', val),
		charindex(' ', val)
FROM		@tbl
ORDER BY	LEFT(val, 1),
		CASE PATINDEX('[A-Z][0-9]%', val)
			WHEN 0 THEN LEN(val)
			ELSE charindex(' ', val)
		END,
		CASE PATINDEX('[A-Z][0-9]%', val)
			WHEN 0 THEN val
			ELSE SUBSTRING(val, 1, charindex(' ', val) - 1)
		END,
		CASE PATINDEX('[A-Z][0-9]%', val)
			WHEN 0 THEN val
			ELSE SUBSTRING(val, charindex(' ', val) + 1, LEN(val))
		END

N 56°04'39.26"
E 12°55'05.63"

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-04 : 13:02:41

great thanks peso

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-08-05 : 03:17:39

Hi Peso,
I got the query solution and then found some other data which cause the problem in query..
can you please help me on that data to be sorted properly i tried with changes but didnt get solution..

Sample data..
F.20b Base: Have At Least One Notebook Brand
F.21a Base: Organizations Owning At Least One Server
F.21b Base: Have More Than One Server Brand
F.21b Base: Have One Server Brand
F.21b Base: Have At Least One Server Brand
Q.1 Scenarios Relevant to Purchase Decisions
Q.10 Level of IT Support
Q.11 Desktop Purchase Experience - Value Customization vs. Simplicity
Q.12 Summary Share Table - Percentage of Desktop and Notebook PCs in Organization
Q.12a % desktop personal computers
Q.12b % notebook personal computers
Q.13 Company Annual Sales
Q.14 When Business Established
Q.15 Organization Type
Q.16 Number of Desktops in Organization
Q.17 Number of Notebooks in Organization
Q.S5a Involvement in Technology Product Purchase Decisions - Notebooks
Q.S5b Involvement in Technology Product Purchase Decisions - Desktops
Q.S5c Involvement in Technology Product Purchase Decisions - Servers
Q.S5d Involvement in Technology Product Purchase Decisions - Storage
Q.12a % desktop personal computers
Q.12b % notebook personal computers
Q11. RCM&D (Baltimore, MD) - RENEWALS: Offers ability to discuss
Q2c. Name Of Auto Service Provider - Jiffy Lube
Q2c. Name Of Auto Service Provider - Pep Boys
Q12d. Service Performed - Meineke
Q14b. Brand In Commercial - Meineke
Q.S5c Involvement in Technology Product Purchase Decisions - Servers
QB7e_18 Brand Imagery - Bacitraycin - Is good for everyone in my
QB7e_19 Brand Imagery - Bacitraycin - Costs more, but is worth it
Q.2d Value of newsletters - Rated on a 5 point scale from - Extremely
Q.2e Company website visit for instructional resources
Q.10X7 - Self Description (Great Escape) - Taking A Little Break Now
Q.10X - Self Description - Summary Of Bottom Box (Does Not Descibe Me
Q8. How would you rate your relationship with Frenkel Benefits (New
c(126,127)
c(134,135)
c(136,137)
Screener:Q.4b How often do you use these products?
Q.21b Primary Server Brand

Is there any generic thing that we can do sorting for some starting characters..

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources