Update Different Groups of Data

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

General SQL Server Forums

New to SQL Server Programming

Update Different Groups of Data

Author

Topic

mathmath
Starting Member

13 Posts

Posted - 2009-06-29 : 03:53:09

Hi All,
I have data like the following:
Stuff Sizes ID
pen s1 100001
pen s2 100002
pen s3 100003
pencil s1 100001
pencil s3 100004
pencil s5 100007
pencil s2 100006
ruler s7 100005
ruler s8 100008
..
..
where each size is coordinate with a unique ID number;
and there are 10 sizes (s1.....s10);

**i need to find out the HIGHEST ID number within each categories of stuff.
e.g pen (100003), pencil (10007),ruler(100008)

How should I do it?

Thank you all for your time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 03:57:09

SELECT Stuff, MAX(ID) FROM Table1 GROUP BY Stuff

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

mathmath
Starting Member

13 Posts

Posted - 2009-06-29 : 04:03:14

quote:
Originally posted by Peso

SELECT Stuff, MAX(ID) FROM Table1 GROUP BY Stuff

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

Thanks.
But i tried using Group by and it doesnt work. something about aggregate function.

ps. nice house

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-06-29 : 04:13:28

quote:
Originally posted by mathmath
Thanks.
But i tried using Group by and it doesnt work. something about aggregate function.

ps. nice house

Why it din't work?
You din't get expected result? or any error?

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 04:13:31

For the provided and known sample data posted 06/29/2009 : 03:53:09
my code works.

If you have another scenario at home or at work (which we don't have access to), please provide all necessary information needed to assist you.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 04:48:27

quote:
Originally posted by Mangal Pardeshi

nice house

Thank you!

Google supports it
http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=56%C2%B004'39.26%22,+12%C2%B055'05.63%22&sll=43.089827,-1.669922&sspn=0.017833,0.040641&g=%2B43%C2%B0+5'+12.99%22,+-1%C2%B0+40'+11.72%22&ie=UTF8&t=k&ll=56.077378,12.917381&spn=0.006814,0.02032&z=16

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

mathmath
Starting Member

13 Posts

Posted - 2009-06-29 : 04:56:30

quote:
Originally posted by Peso

For the provided and known sample data posted 06/29/2009 : 03:53:09
my code works.

If you have another scenario at home or at work (which we don't have access to), please provide all necessary information needed to assist you.

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

yes, it works now.
I do have more columns in the table, i have export the data i need to another table before i can group by.
Thanks for the help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 04:59:55

You want to show more columns for same record?

SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Stuff ORDER BY ID DESC) AS recID FROM Table1
) AS d
WHERE recID = 1

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