Displaying data based on the alphabet name

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)

Displaying data based on the alphabet name

Author

Topic

sudha12345
Starting Member

47 Posts

Posted - 2009-05-21 : 01:10:32

i have an requirement which i has to display the data in different files based on alphabe name

the data in one of the column is as below
abc
bcd
def
fed
**fed
*fed
(fed

ghi
ijk
klm
lmn

mns
prs
rsd

Now i want to get the data as below
1st file

abc
bcd
def
fed

2nd file
ghi
ijk
klm
lmn

3rd file
mns
prs
rsd

4th file
**fed
*fed
(fed

can any one help me how can we write a selec query to acheive this

Sudhakar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 01:16:27

select * from (select *, ntile(3) as recid from table where col1 like '[a-z]%') as d where recid = 1

select * from (select *, ntile(3) as recid from table where col1 like '[a-z]%') as d where recid = 2

select * from (select *, ntile(3) as recid from table where col1 like '[a-z]%') as d where recid = 3

select * from table where col1 not like '[a-z]%'

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

sudha12345
Starting Member

47 Posts

Posted - 2009-05-21 : 01:41:03

Thanks for u r reply

while executing the query it is displaying an error
Incorrect syntax near 'ntile', expected 'OVER'.

Can you Help me on this

Sudhakar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 01:44:50

replace all these

ntile(3) as recid

with

ntile(3) over (order by col1) as recid

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

sudha12345
Starting Member

47 Posts

Posted - 2009-05-21 : 02:10:26

Thanks a lot. it is fine and working.

if we want to pass a parameter instead of HardCodind

we are selecting the data with the criteria like '[a-z]%

can we use 1 parameter for a and 1 one parameter z

we wan to select the data with the criteria like '[parameter-parameter]% instead of a and z

is it possible to do that

if possible how can we acheive that

Sudhakar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 02:30:12

where col1 like '[' + @param1 + '-' + @param2 + ']%'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources