Help plz - 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

General SQL Server Forums

New to SQL Server Programming

Help plz

Author

Topic

jhon11
Starting Member

42 Posts

Posted - 2008-04-10 : 10:33:58

hello All,

It is kind of easy one but for me it seems difficult
and I cant think of it

my results are like

ID column1 column2 column3
1 15 null null
1 null 6 null
1 null null 9

desired results
ID col1 col2 col3
1 15 6 9

thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 10:35:57

SELECT ID, MAX(Column1), MAX(Column2), MAX(Column3)
FROM Table1
GROUP BY ID

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-10 : 10:36:05

select id,max(column1) as col1,max(column2) as col2,max(column3) as col3 from your_table
group by id

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 10:36:14

[code]select ID, max(col1) as col1, max(col2) as col2, max(col3) as col3
from table
group by ID[/code]

Double

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 10:37:12

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 10:39:24

quote:
Originally posted by harsh_athalye

Double

again!

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 10:40:38

Yes, I am bored right now

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-10 : 10:41:24

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 10:41:50

quote:
Originally posted by Peso

E 12°55'05.25"
N 56°04'39.16"

How can you be sniped Peter? You were the first to reply, so we are sniped by you!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 10:43:41

Well, I am bored right now and don't much else to do.
Have been running an UPDATE statement for 13 hours now...

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-10 : 10:44:25

Me too. Waiting for an Autosys job to be finished.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 04:07:18

quote:
Originally posted by Peso

Well, I am bored right now and don't much else to do.
Have been running an UPDATE statement for 13 hours now...

E 12°55'05.25"
N 56°04'39.16"

How big is the table?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 07:05:21

Only 25 million records. But updated have to run about 320 times...

E 12°55'05.25"
N 56°04'39.16"

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-04-11 : 08:16:10

if ur table has only the records below

ID column1 column2 column3
1 15 null null
1 null 6 null
1 null null 9

then for ur result, try...its null magic
select id,min(column1) as col1,avg(column2) as col2,max(column3) as col3 from your_table
group by id

ok
thanks......

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 08:24:31

How it is different from the solutions posted above? and why min(), avg() and max()?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-04-11 : 08:34:53

hi
its same idea and gives same result.
but in a fancy way...
but it is only applicable for sample data of above pattern....

ok

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-11 : 08:34:54

quote:
Originally posted by harsh_athalye

How it is different from the solutions posted above? and why min(), avg() and max()?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Becuase that solution is specific to that data only

Madhivanan

Failing to plan is Planning to fail

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-04-11 : 08:35:55

yes madhi
u r right...

okk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources