How to create cursor?

[19511950]

Hi,

I have a table that has id,cat & num fields.
Each cat has multiple ids, Num is 0
I need to create sp that will pick top 1 in each category and update field Num to 1.

id cat num
-- --- ---
1 A 0
2 A 0
7 B 0
6 B 0
2 C 0
7 C 0

Regards,

Mark

 

[dbomrrsm]

what do you mean by top - the highest id by category ? or the lowest id by category ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Pattycake245]

What do you mean by top 1? The lowest id per category. If this is the case, then a cursor is not needed. Something liek below should work:

update a
set a.num = 1
from table a join
(select min([id]) [id], cat, num from table group by cat,num) b
on a.cat=b.cat and a.[id]=b.[id]

Tim

 

[19511950]

I need pass cat in each statement
update tbl1 Set num=1 where id in
(select top 1 from tbl1 where cat='A')
update tbl1 Set num=1 where id in
(select top 1 from tbl1 where cat='B')
update tbl1 Set num=1 where id in
(select top 1 from tbl1 where cat='C')

 

[dbomrrsm]

Pattycakes solution will work if you want the minimum ie i for cat = a or 6 where cat = b - if you want the highest then use max in place of min.

nice shout pat

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbomrrsm]

BTW - you can usually (in 99.9% of cases) find a solution like this without using a cursor - cursors - you will find - especially on large db's so resource intensive that you might as well change the values manually.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[19511950]

I have about 50 cat's and 500 id's.
From each cat i need only the top 1.
How to paste cat to each statement to get id?

 

[Pattycake245]

Again, as dbomrrsm and I said, what defines top 1; lowest, highest or just the first record for each cat the query comes across? Does it matter which record gets updated, because the method you've shown would take a while if there are a lot of records.

Tim

 

[Catadmin]

There is a way to use the Distinct keyword and the Top keyword together, but I don't have it working exactly.

Will get back to you as soon as I do if someone else doesn't beat me to the punch. @=)




Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[dbomrrsm]

going to bed - I aint gona punch first

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Catadmin]

AHA! I was wrong. Don't use Distinct & Top. Use Distinct and Max/Min.

Here's the Northwind code I tested it with:

select Distinct CategoryID, Max(Productname)
from products 
group by CategoryID

Returns the following list:

CategoryID ProductName

1 Steeleye Stout
2 Vegie-spread
3 Zaanse koeken
4 Raclette Courdavault
5 Wimmers gute Semmelknödel
6 Tourtière
7 Uncle Bob's Organic Dried Pears
8 Spegesild


I even verified that it caught all the categoryIDs before I posted this.

Hope this helps!




Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Pattycake245]

Catadmin, you won't need distinct when you have a group by since the IDs will be distinct already through the group by.

Tim

 

[Catadmin]

Well, I'll be darned (and knitted and pearled...)

You are correct. I've been so busy trying to learn the complicated stuff (I've reached the chapter on Cursors in my book), that I done forgot all the simple stuff.

Thank you for that correction, Pattycake245. I appreciate it.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Pattycake245]

No problem, and if I were you I wouldn't spend much time on the cursors chapter, since you will hardly ever use it as 99% of the time a set-based solution will do the trick faster and more efficiently. Trust me, you'll see the hundreds of posts reiterating this point.

Tim

 

[Catadmin]

Yeah, but if I don't learn it, I'll run into that 1% of the time. Better to know it and know why it *won't* work then to not know it and be unable to come up with a solution.

Murphy really really likes me. @=)



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Pattycake245]

good point!

 

[bassguy]

Hi,
I am sorry to be rude this is not meant with any offense intended. But how do you get to be a MCDBA without knowing cursors?

maybe the certification is not what I thought.

Bassguy

 

[Catadmin]

The Cert tests only require you to know the basics. And it's been over two years since I've been required to do anything other than DBA tasks and general (read simple) T-SQL Ad Hoc queries. Now that I'm in a Designer/DBA type job, I need to go back and relearn everything from the start forward.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!