help on a distinct command - 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 2000 Forums

Transact-SQL (2000)

help on a distinct command

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-06-06 : 07:55:40

output=

Name-------Money--------location
jack--------1000--------xxx
jack--------1500---------yyy
joe--------1000---------zzz

result will be:

Name-------Money--------Location
jack--------2500---------yyy >>>yyy because money is higher than other jack
joe--------1000----------zzz

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 07:57:21

[code]select Name, sum(Money), max(location)
from table
group by Name[/code]

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-06-06 : 08:05:19

damn right
i am coding the project for about 5 hours without stop and i cant think clearly sometimes
thx again :D

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-06-06 : 08:10:03

but how max location orders itself ?
it doesnt work i think.

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 08:11:13

quote:
Originally posted by bilencekic

but how max location orders itself ?
it doesnt work i think.

MS BLESS US

what do you mean ?

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-06-06 : 08:17:49

your query doesnt work
what max(location) do?
alphabetical order??
because location is a text

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-06-06 : 08:22:51

yes it does alphabetical order i think.
but in my query i want result with the location which pays the much money than others

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-06 : 08:28:08

Oh i see.


select Name, sum(Money), max(location),
       (select top 1 location from table x where x.Name = t.Name order by Money desc)
from   table
group by Name

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-06 : 08:30:06

Give Me The Datatype for these things
Name-------Money--------location

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-06-06 : 08:47:59

ok i have fixed it thx

MS BLESS US

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources