same result without cursor - 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)

same result without cursor

Author

Topic

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-07 : 20:54:49

Hi Guys,
Here is my code & I have two questions?
1. Can i get the same result without using a cursor and temp tables
2.Once i get the expected result then how to find the most popular ID?

declare @name_id varchar(20)
declare detail_name_id cursor
For select convert(varchar, detail_name_id) from di_webclick_statistics
where convert(varchar, detail_name_id) !='0'AND (len(convert(varchar,detail_name_id))< 6)
open detail_name_id
fetch next from detail_name_id INTO @name_id
while @@fetch_status=0
begin
fetch next from detail_name_id INTO @name_id
select @name_id as detail_name_id
end
declare @detail_name_id varchar(20)
set @detail_name_id =(select convert(varchar, detail_name_id) from di_webclick_statistics
where len(convert(varchar,detail_name_id))>5)
declare @len int
set @len=len(@detail_name_id)
declare @i int
set @i=1
while(@i <@len)
begin
select substring(@detail_name_id,@i,5)as detail_name_id
set @i= @i+ 6
end

close detail_name_id
deallocate detail_name_id

I want these values in one table so that i can find out the most popular id.
Thank you in advance

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-10-07 : 21:56:06

For stuff like this, it's much easier if you give us some DDL (Create Table statements) and what you are trying to achieve.

It looks to me like this code won't work properly at all, I'm not exactly sure why you are doing what you are doing.

To get the most popular ID, try this :

SELECT detail_name_id, Count(*)
FROM di_webclick_statistics
GROUP BY detail_name_id
ORDER BY Count(*) Desc

Damian

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-07 : 22:06:28

Merkin, what you are saying is absolutely right.Select statement which you gave i have already used that statement when i didn't have this type of problem like i have multiple values in a couple of fields for example
Detail_name_id
12344
12343
11234 23456 22223 23445
45678
234567 23456 78909
23456
So i would have used the same select statement what you told me but this does not work it a field has more than one value.Hope you understand why am i using cursor? Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-07 : 22:09:51

I don't understand your code either. This part is really confusing:



while @@fetch_status=0
begin
	fetch next from detail_name_id INTO @name_id

	select @name_id as detail_name_id 
end

You didn't do anything in there except get the last value in the cursor into that variable. Please explain what you are trying to do with data and an expected result set.

Tara

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-07 : 22:26:58

Tara, Please refer to my previous reply. With this code i am able to split those values from those fields where there are more than one values and put them as single values.Now there are no multiple values in any field but i want this result without using a cursor. Hope i am clear. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-07 : 22:29:06

I still don't understand. Please post some sample data of your table and the expected result set using that sample data.

Tara

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-10-07 : 22:32:01

So you have multiple values in one column of your table ???

That's break rule #1 of database design, a redesign is BY FAR your best option, it will make everything else faster and easier.

Damian

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 22:38:13

Just for everyone's information... this a continuation of the following thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40941

He wanted something simpler than my solution... which I can sympathize with, however, I don't know of anything simpler...

Good luck to you man.

Corey

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-07 : 22:39:39

Merkin, that would be my last option and that is going to be really painful.I really want to use simple select statement with group by and order by. That would work provided there are no multiple values.I am able to break those values also with the code i mentioned but i really don't want to use cursors.Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-07 : 22:42:58

You can't use a simple query for this due to the bad database design. You should bite the bullet and fix the design, then you'll be able to write simple statements that will perform way better than any cursor that you can write. And why not use Corey's complex set-based solution?

Tara

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 22:43:05

oh and I managed to get this to work without the split table variable...

EDIT: do remember the part in blue is just setup for test data...
and the part in green is one of many ways to get a numbers table
and the new parts are in bold



Declare @myTable table (id int identity(1,1), myVal varchar(1000))
Insert Into @myTable
Select '78770'
Union Select '78762'
Union Select '78764 78758 78758'
Union Select '78779'
Union Select '78769'
Union Select '0'
Union Select '0'


Declare @numbers table (n int identity(0,1), blah int)
Insert Into @numbers
Select 0 from master.dbo.syscolumns

Declare @maxLength int
Select @maxLength = max(len(myVal)) From @myTable

Select splitVal, Cnt = count(*) 
From
	(
	Select
		id,
		myVal,
		posA,
		posB = min(posB),
		splitVal = rtrim(ltrim(substring(myVal,posA,isnull(min(posB),len(myVal))-posA+1)))
	From
		(		
		Select A.id, A.myVal, posA = B.n, posB = C.n
		From @myTable A
		Inner Join (Select * From @numbers Where n between 0 and @maxLength) B
		On Substring(A.myVal,B.n,1)=' '
		and len(rtrim(ltrim(A.myVal)))>=B.n
		Left Join (Select * From @numbers Where n between 0 and @maxLength) C
		On B.n+1<=C.n
		and Substring(A.myVal,C.n,1)=' '
		and len(rtrim(ltrim(A.myVal)))>=C.n
		) Z
	Group by id, myVal, posA
	) as A
Group By splitVal
Order By 2 desc, 1 asc

Corey

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-07 : 22:57:01

Corey, for the time being i am using your solution only but still wanted really simple solution if possible. I really appreciate everyone's help. Thanks

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-10-07 : 23:19:45

You can't have it both ways.

The simple and best solution is to redesign the table. Good design should come first, not last.

Damian

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 23:21:10

If you figure out a simpler solution... I want to know!!

This is one of those things that seems simple, but really isn't

Corey

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-07 : 23:29:31

Hey Guys,
I am really trying very hard to come up with a simple solution. Its not that i am not liking your code Corey, To be very honest your code is working fine but look wise it is so complicated.I really wanted it to look simple also so that i don't get scared.Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-07 : 23:30:43

You won't find a simple solution for your problem without redesigning your database.

Tara

dreamaboutnothing
Starting Member

47 Posts

Posted - 2004-10-07 : 23:35:18

Tara, may be you are right.If i can't figure it out then i will redesign my database and if i am able to find any simple solution i would post it. Thanks.I appreciate it.

ten2the6
Starting Member

7 Posts

Posted - 2004-10-08 : 11:42:12

quote:
Originally posted by Merkin

So you have multiple values in one column of your table ???

That's break rule #1 of database design, a redesign is BY FAR your best option, it will make everything else faster and easier.

Damian

Damian,

The last rule of database design is that you start breaking some rules so that you get better performance or less complicated queries.

Solution to this problem can be found at the link below.

http://www.sqlteam.com/item.asp?ItemID=2652

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-08 : 12:04:19

quote:
Originally posted by ten2the6

quote:
Originally posted by Merkin

So you have multiple values in one column of your table ???

That's break rule #1 of database design, a redesign is BY FAR your best option, it will make everything else faster and easier.

Damian

Damian,

The last rule of database design is that you start breaking some rules so that you get better performance or less complicated queries.

Solution to this problem can be found at the link below.

http://www.sqlteam.com/item.asp?ItemID=2652

NO !

You should NEVER purposely design your database in the manner described in this thread, thinking that the link you posted above is the simplest solution or will be the least complicated query.

The least complicated query and the best performance is obtained in this case by normalizing your database and not storing more than 1 value concatenated together in a column.

Did you see Merkin's original query, the first reply? THAT is the simplest solution to this, and it runs perfectly fine on a well designed database.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-08 : 12:56:10

Come on Jeff, tell us how you really feel.

And with Saux on the verge of a sweep and all...

Listen, to back pedal for the good Dr., A logical model will undergo a transformation into a physical model for performance reasons...

Dr?

BUT, not like this....

Brett

8-)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-08 : 13:28:15

Using Corey's DDL:


SELECT Count(val) occurance, val
FROM
(
	SELECT NullIf(SubString(' ' + myVal + ' ' , n , CharIndex(' ' , ' ' + myVal + ' ' , n) - n) , '') val
	FROM @numbers, @myTable 
	WHERE n <= Len(' ' + myVal + ' ') AND SubString(' ' + myVal + ' ' , n - 1, 1) = ' ' 
) d
WHERE val is not null
GROUP BY val
ORDER BY Count(val) desc

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources