sql query - 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 2000 Forums

Transact-SQL (2000)

sql query

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-07-11 : 19:00:45

Hi,

Select query in code is returning desired results.
can i get it without using #number table.


create table #temp (a char(10),b char(10))
insert into #temp values('TEST1','TEST1')
insert into #temp values('TEST1','TEST1')
insert into #temp values('TEST1','TEST1')
insert into #temp values('TEST2','TEST2')
insert into #temp values('TEST2','TEST2')
insert into #temp values('RAJ','RAJ')
insert into #temp values('RAJ','RAJ')
insert into #temp values('RAJ','RAJ')
insert into #temp values('RAJ','RAJ')
insert into #temp values('RAJ','RAJ')



create table #number (n int)
insert into #number values (1)
insert into #number values (2)
insert into #number values (3)
insert into #number values (4)
insert into #number values (5)
insert into #number values (6)


select t.a,t.b, #number.n
from
            (select a,b,count(*) c from #temp group by a,b) t
cross join #number
where #number.n<=t.c



drop table #temp
drop table #number

Thanks

mk_garg

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-11 : 19:06:21

Can you get what without using the number table? What is the expected output?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-07-11 : 19:15:55

output expected is output shown by current select query in code.
i dont want to use #number table to achieve that

mk_garg

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-11 : 19:29:27

Check if this helps: http://www.sqlteam.com/article/returning-a-row-number-in-a-query

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-07-11 : 19:53:30

Nope this will not help.

mk_garg

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-11 : 20:03:43

Is there any PK on the table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2007-07-11 : 20:12:05

no

mk_garg

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 21:08:09

Do the numbering in you front end !

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 21:09:21