Set Column Value (Based On Minum Value)

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

Set Column Value (Based On Minum Value)

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 12:26:11

I have a table of data such as below

Table (- = column)
id/firstname/description/school/primary
1111 - joe - test guy - 12 - no
1111 - joe - test guy - 10 - no
1111 - joe - test guy - 7 - no
1112 - tom - test person - 8 - no
Is there a way i can run a query to set the 'primary' column to 'yes' based on the lowest value out of all the uniq records for each person?

So for example, it will grab all the 1111 id's, look at all the school values, the based on the lowest value, it will put a 'yes' in the primary column?

Thanks guys!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:28:23

are you using SQL 2005?

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

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 12:37:56

SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:39:59

cool here you go


UPDATE t
SET t.primary='yes'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY id,firstname,description ORDER BY school ASC) AS Seq,primary
      FROM Table)t
WHERE t.Seq=1

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

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 12:48:18

Hummm that changed all my secondary_flag columns to 'yes'

UPDATE sis_students
SET sis_students.secondary_flag='yes'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY external_id,school ORDER BY school ASC) AS Seq, secondary_flag
FROM sis_students)t
WHERE t.Seq=1

It should do something like this below.

Original Table
1111 - joe - test guy - 12 - no
1111 - joe - test guy - 10 - no
1111 - joe - test guy - 7 - no
1112 - tom - test person - 8 - no
1112 - tom - test person - 55 - no

Converted Table
1111 - joe - test guy - 12 - no
1111 - joe - test guy - 10 - no
1111 - joe - test guy - 7 - yes
1112 - tom - test person - 8 - yes
1112 - tom - test person - 55 - no

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:50:03

quote:
Originally posted by jjmusicpro

Hummm that changed all my secondary_flag columns to 'yes'

UPDATE sis_students
SET sis_students.secondary_flag='yes'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY external_id,school ORDER BY school ASC) AS Seq, secondary_flag
FROM sis_students)t
WHERE t.Seq=1

what as per sample data you told primary rite?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:51:18

quote:
Originally posted by jjmusicpro

Hummm that changed all my secondary_flag columns to 'yes'

UPDATE sis_students
SET sis_students.secondary_flag='yes'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY external_id,school ORDER BY school ASC) AS Seq, secondary_flag
FROM sis_students)t
WHERE t.Seq=1

It should do something like this below.

Original Table
1111 - joe - test guy - 12 - no
1111 - joe - test guy - 10 - no
1111 - joe - test guy - 7 - no
1112 - tom - test person - 8 - no
1112 - tom - test person - 55 - no

Converted Table
1111 - joe - test guy - 12 - no
1111 - joe - test guy - 10 - no
1111 - joe - test guy - 7 - yes
1112 - tom - test person - 8 - yes
1112 - tom - test person - 55 - no

please see my suggestion and one you used. there are lot of differences. either use as i suggested or give us correct query.

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

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 12:53:18

quote:
Originally posted by visakh16

quote:
Originally posted by jjmusicpro

Hummm that changed all my secondary_flag columns to 'yes'

UPDATE sis_students
SET sis_students.secondary_flag='yes'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY external_id,school ORDER BY school ASC) AS Seq, secondary_flag
FROM sis_students)t
WHERE t.Seq=1

what as per sample data you told primary rite?

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

Yes may table layout is like below.

table sis_students
id (auto increment number)
external_id (id of user)
school (school number)
secondary_flag (yes/no) by default these are all 'no'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:57:23

[code]
UPDATE t
SET t.secondary_flag='yes'
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY external_id ORDER BY school ASC) AS Seq, secondary_flag
FROM sis_students)t
WHERE t.Seq=1
[/code]
i hope your table structure wont change again!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources