Temp Table or Cursor help - 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 2008 Forums

Transact-SQL (2008)

Temp Table or Cursor help

Author

Topic

txgeekgirl
Starting Member

18 Posts

Posted - 2012-09-12 : 18:23:40

I need a refresher. I have worked extensively in SQL in VB/VBA and FoxPro but am now needing to pretty much straight code SQL in Management Studio - create a stored procedure.

I need a SQL Select to grab Max data that can be used in an Update.

In SQL for VBA, I would do a recordset and use a SQL UPDATE statement.
In FoxPro, I could build cursors.

How would I do this in straight SQL?

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-12 : 18:37:31

Give us your schema (Tables) and expected output.

-Chad

txgeekgirl
Starting Member

18 Posts

Posted - 2012-09-12 : 19:40:14

I was able to accomplish with a Temp Table - now the issue I have is The MIN


SELECT ID, 
MIN([Year]) as MyYear,
MIN([Code]) as MyCode
FROM Profile
WHERE [TYPE] = 'U'
GROUP BY ID

I get the Minimum Val for Year but not necessarily the corresponding value for Code. I am actually getting the MIN value ever for Code even if it happened in another year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 22:26:55

[code]
SELECT p.ID,p.[Year],p.[Code]
FROM Profile p
INNER JOIN (
SELECT ID,
MIN([Year]) as MyYear
FROM Profile
WHERE [TYPE] = 'U'
GROUP BY ID)p1
ON p1.MyYear = p.[Year]
AND p1.ID = p.ID
WHERE p.[TYPE]='U'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 04:46:50

Also try
select id,[year],[code] from
(
select *, row_number() over (partition by [year] order by [year],[code]) as sno
from profile
where [type] = 'u'
) as t
where sno=1

Madhivanan

Failing to plan is Planning to fail

txgeekgirl
Starting Member

18 Posts

Posted - 2012-09-13 : 11:09:12

ID is not unique and there can actually be several instances - if they have 20 yrs worth of data, they can have up to 60 rows of data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 11:19:06

quote:
Originally posted by txgeekgirl

ID is not unique and there can actually be several instances - if they have 20 yrs worth of data, they can have up to 60 rows of data.

so are you telling there can be multiple records per ID per year?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

txgeekgirl
Starting Member

18 Posts

Posted - 2012-09-13 : 11:41:21

Exactly. Up to 3.

txgeekgirl
Starting Member

18 Posts

Posted - 2012-09-13 : 12:07:51

I got this to work by doing this:


Select ID, 
   MIN(Year + ' ' + code) as mycode
   FROM Profile
   WHERE [TYPE] = 'U'
   GROUP BY ID

Then parsing it on the Update.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 12:09:12

quote:
Originally posted by txgeekgirl

I got this to work by doing this:

Select ID,
MIN(Year + ' ' + code) as mycode
FROM Profile
WHERE [TYPE] = 'U'
GROUP BY ID

Then parsing it on the Update.

ok...good

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-13 : 12:11:14

To expand on madhivanan's example, can't you just add ID to the PARTITION clause so you don't hae to parse strings?

select id,[year],[code] 
from
(
    select *, row_number() over (partition by id, [year] order by id, [year],[code]) as sno
    from profile
    where [type] = 'u'
) as t
where sno=1

txgeekgirl
Starting Member

18 Posts

Posted - 2012-09-13 : 12:27:58

I inherited a bit of a mess - just changed jobs and went from a house that used Primary Keys on every table to one that doesn't. I think the software pckg attached to it is sloppily done and the tables reflect it.

With no unique keys, I think Madhivanan's example would not work.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-13 : 12:32:56

Without beging able to see your data, I guess we'll have to take your word for it. Good luck! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources