SQL Server: Select only one row of rows that has the same ID on some coulmn

Question

I have a table that has 3 columns: - ID - FROM - TO And i have data like that

-----------------------
ID   |   FROM   |   TO
1    |   2      |   1
2    |   5      |   1
3    |   7      |   1
4    |   2      |   1
5    |   2      |   1
6    |   9      |   1
7    |   3      |   1
8    |   4      |   1
9    |   5      |   1

I would like to create a query that selects all rows where TO = 1 and i don't want to display rows that was previously retrieved, for example i have multiple rows where FROM = 2 and TO = 1, i just need to retrieve that row only once.

My table doesn't really look like this but i am giving a small example because my aim is to collect all FROM numbers but without any redundancy.

Do you need to return/display other columns besides FROM and TO? — db_brad, Sep 05 '13 at 19:03
Do you need the `ID` column?, if so, which one should you choose? — Lamak, Sep 05 '13 at 19:05

score 1 · Answer 1 · answered Sep 05 '13 at 18:54

1

Use DISTINCT

SELECT DISTINCT from,to FROM yourTable WHERE to = 1

answered Sep 05 '13 at 18:54

Declan_K

6,726
2
19
30

score 1 · Accepted Answer · answered Sep 05 '13 at 18:56

1

use distinct keyword

select distinct m.from,m.to from mytable as m;

answered Sep 05 '13 at 18:56

danisius

597
1
5
19

score 1 · Answer 3 · edited May 23 '17 at 10:31

You just have to group by the columns you want to display:

select [from] from mytable group by [from]

If you want to see how many froms you have all you have to do is:

select [from], count(*) from mytable group by [from]

You could use distinct but it would slower than group by but require more memory.

Please read here if you want an explanation on the difference between group by and distinct: Huge performance difference when using group by vs distinct

score 1 · Answer 4 · answered Sep 05 '13 at 19:04

Not sure what exactly you meant select distinct [FROM] from TableName where [TO] = 1

OR

may be you need single row for every distinct [FROM] value for given [TO] ?

;with cte as (
    select ID, [FROM], [TO],
        rn = row_number() over (partition by [FROM] order by ID)
    from TableName
    where [TO] = 1
)
select ID, [FROM], [TO]
from cte
where rn=1

SQL Server: Select only one row of rows that has the same ID on some coulmn

4 Answers4