problem with Select distinct - 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

SQL Server Development (2000)

problem with Select distinct

Author

Topic

happyboy
Starting Member

21 Posts

Posted - 2009-03-11 : 23:04:58

I would like to select distinct on some column, and get other information from other column (not distinct):

SELECT A,B,DISTINCT C FROM TABLE

I know that SQL accept only distinct just after "select"
Please help me!

Table "TLQuyDoi"
cifno ten DCQuyDoi
1 a 10
1 b 10
2 s 9

I like to get only distinct on (cifno and DCQuyDoi) , besides get the value of "Ten"

The resuls I hope like this

cifno ten DCQuyDoi
1 a 10
2 s 9

Here is my code


create table #KetQua (cifno varchar(10),SumDCQuyDoi float)
	
Insert into #KetQua
Select cifno,sum(DCQuyDoi)
From TLQuyDoi t
Group by cifno
Having sum(DCQuyDoi) >= @GiaTri
Order by cifno,sum(DCQuyDoi)

Select distinct k.cifno,t.ten,k.SumDCQuyDoi
From #KetQua k,TLQuyDoi t
Where k.cifno=t.cifno	Order by k.SumDCQuyDoi desc,k.cifno asc

drop table #KetQua

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-12 : 00:42:58

declare @TLQuyDoi Table (cifno int,ten varchar(2),DCQuyDoi int)
insert into @tlquydoi select 1, 'a', 10
insert into @tlquydoi select 1, 'b', 10
insert into @tlquydoi select 2, 's', 9

Select distinct cifno,min(ten),DCQuyDoi
From @TLQuyDoi
group by cifno,DCQuyDoi
order by cifno

happyboy
Starting Member

21 Posts

Posted - 2009-03-12 : 02:22:45

thanks for reply

your code is better!!! and shorter.

I never used max(string) before.

could you please explane me why sql do that select.

because when i use select min('abc') --> it just return abc
thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-12 : 02:25:30

see booksonline for aggregate functions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources