Question regarding pulling existing 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

Question regarding pulling existing data

Author

Topic

scrosby
Starting Member

4 Posts

Posted - 2009-07-17 : 13:25:33

Hello,

I am new to SQL I am looking for a way to pull a max value from a field in an existing table into a new field in a new table. I want to pull the newest badge number for a user from the exiting table into a new one, because each user has multiple badge numbers, but I only want the newest one. Is this possible? If so where would I put in here. Thanks for any help you can offer.

SELECT PERSON.PERSONNUM AS PERSONMUN, PERSON.PERSONID AS PERSONID, PERSON.FIRSTNM AS FIRSTNM, PERSON.LASTNM AS LASTNM, BADGEASSIGN.BADGENUM AS BADGENUM, BADGEASSIGN.UPDATEDTM AS UPDATEDTM, BADGEASSIGN.BADGEASSIGNID AS BADGEASSIGNID

FROM PERSON INNER JOIN
BADGEASSIGN ON PERSON.PERSONID = BADGEASSIGN.PERSONID
ORDER BY BADGEASSIGN.UPDATEDTM DESC

UPDATE NEWBADGE11
SET PERSONID =
(SELECT PERSON.PERSONID
FROM PERSON
WHERE PERSON.PERSONID = NEWBADGE11.PERSONID)
WHERE EXISTS
(SELECT PERSON.PERSONID
FROM PERSON
WHERE PERSON.PERSONID = NEWBADGE11.PERSONID)

ALTER TABLE NEWBADGE11
ADD LICENSURE varchar(50)

ALTER TABLE NEWBADGE11
ADD TITLE varchar(50)

ALTER TABLE NEWBADGE11
ADD DEPARTMENT varchar(50) This is pulling what I want, but I dont want multiple

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 13:29:26

are you using sql 2005 or higher?

scrosby
Starting Member

4 Posts

Posted - 2009-07-17 : 13:31:07

Yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 13:40:02

use this


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PERSON.PERSONNUM ORDER BY BADGEASSIGN.UPDATEDTM DESC) AS Seq,
PERSON.PERSONNUM AS PERSONMUN, PERSON.PERSONID AS PERSONID, PERSON.FIRSTNM AS FIRSTNM, PERSON.LASTNM AS LASTNM, BADGEASSIGN.BADGENUM AS BADGENUM, BADGEASSIGN.UPDATEDTM AS UPDATEDTM, BADGEASSIGN.BADGEASSIGNID AS BADGEASSIGNID 


FROM PERSON INNER JOIN
BADGEASSIGN ON PERSON.PERSONID = BADGEASSIGN.PERSONID
)t
WHERE Seq=1

scrosby
Starting Member

4 Posts

Posted - 2009-07-17 : 14:03:38

Thanks for the reply and forgive my mis information, we are actually using SQL 2000 and it was Studio Express 2005 I was seeing. Sorry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 14:08:13

sql 2000 solution

SELECT 
PERSON.PERSONNUM AS PERSONMUN, PERSON.PERSONID AS PERSONID, PERSON.FIRSTNM AS FIRSTNM, PERSON.LASTNM AS LASTNM, BADGEASSIGN.BADGENUM AS BADGENUM, BADGEASSIGN.UPDATEDTM AS UPDATEDTM, BADGEASSIGN.BADGEASSIGNID AS BADGEASSIGNID INTO #Temp
FROM PERSON INNER JOIN
BADGEASSIGN ON PERSON.PERSONID = BADGEASSIGN.PERSONID

SELECT t.*
FROM #Temp t
INNER JOIN (SELECT PERSONMUN,MAX(BADGENUM) AS LATEST
            FROM #Temp
            GROUP BY PERSONMUN) t1
ON t1.PERSONMUN=t.PERSONMUN
AND t1.LATEST=t.BADGENUM

scrosby
Starting Member

4 Posts

Posted - 2009-07-17 : 14:26:12

Now I am getting this

The column prefix 'BADGEASSIGN' does not match with a table name or alias name used in the query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 14:31:00

quote:
Originally posted by scrosby

Now I am getting this

The column prefix 'BADGEASSIGN' does not match with a table name or alias name used in the query.

show your query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources