CODEWITHSUNDEEP
sqlsql-server
Dolphin
Dolphin

Reputation: 39025

Distinct multiple columns

I want to select the SQL Server datatype and name, but it have columns replicate, this is my SQL:

with typesum
as
(
    select sc.xtype,sc.[length],st.name
    from syscolumns sc,systypes st
    where sc.xtype=st.xtype
    group by sc.xtype,sc.[length],st.name
)
select distinct tsa.xtype,tsb.name,tsb.[length] 
from typesum tsa left join typesum tsb
on tsa.xtype=tsb.xtype

It has many replicate data.

And I use another way:

select distinct (sc.xtype,st.name) from syscolumns sc
left join systypes st
on sc.xtype=st.xtype
group by sc.xtype,st.name

It doesn't work.

My question is: how can I distinct multiple columns by distinct one columns and get the unique data?An result has 10 columns,and i want to distinct column 1 as standard, and other columns data should be deleted.

For example:

1         1
1         1
2         2
2         2

the result is:

1         1
2         2

maybe sample sql can solve is better.Thank you.

Upvotes: 0

Views: 118

Answers (1)

podiluska
podiluska

Reputation: 51514

You don't need both group by and distinct

Either

select distinct sc.xtype,st.name 
from syscolumns sc
left join systypes st
on sc.xtype=st.xtype

or

select sc.xtype,st.name 
from syscolumns sc
left join systypes st
on sc.xtype=st.xtype
group by sc.xtype,st.name

should both work

Upvotes: 3

Related Questions