help on a distinct function - 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 function

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-07-26 : 12:04:33

table
ID-----WordID-----Word-----Value-----Who
1-----15-----play-----1100-----jack
2-----15-----play-----1200-----joe
3-----16-----work-----1000-----maria
4-----16-----work-----1400-----black
5-----17-----do-----1200-----brown

ID is identity and primarykey
i want to word that highst values paid by players
result will be like this.
ResultID-----ID-----WordID-----Word-----Value-----Who
1-----1-----15-----play-----1200-----joe
2-----4-----16-----work-----1400-----black
3-----5-----17-----do-----1200-----brown

i tried max but i coulndt do it :D helppp

MS BLESS US

anand_d
Starting Member

9 Posts

Posted - 2006-07-26 : 13:11:10

select w1.* from worddata w1
inner join
(
select word, max(value)as value from worddata
group by word
) w2
on w1.word=w2.word and w1.value=w2.value
order by id

------- hope this works....a soln i can think of...

Forum Experts: Any other thoughts.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-26 : 13:17:36

Or...

--data
declare @t table (ID int, WordID int, Word varchar(10), Value int, Who varchar(10))
insert @t
          select 1, 15, 'play', 1100, 'jack'
union all select 2, 15, 'play', 1200, 'joe'
union all select 3, 16, 'work', 1000, 'maria'
union all select 4, 16, 'work', 1400, 'black'
union all select 5, 17, 'do', 1200, 'brown'

--calculation
select * from @t a
where Value = (select max(Value) from @t where WordID = a.WordID)
order by id

/*results
ID          WordID      Word       Value       Who        
----------- ----------- ---------- ----------- ---------- 
2           15          play       1200        joe
4           16          work       1400        black
5           17          do         1200        brown
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-07-26 : 17:15:55

yep but there are hunders of records
how can i union all

MS BLESS US

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-27 : 04:51:53

You don't. You only need the calculation section. So you will have...

select * from YourTable a
where Value = (select max(Value) from YourTable where WordID = a.WordID)
order by id

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 09:11:58

I have seen some questioners think that they need to use union all to prepare data without knowing that union all is given an example

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources