Retrieve distinct records on the basis of other columns

[Yogesh Sharma]

I want to select that rollnos which have distinct name & address.
create table studento(roll int,name varchar,address varchar(5))
insert into studento values(1,'A','ADD1')
insert into studento values(2,'B','ADD2')
insert into studento values(3,'A','ADD3')
insert into studento values(4,'A','ADDR3')
select name from studento

I have tried the foll. query to retrive the roll that has distinct name,But query is still returning all roll numbers.

select roll from studento where name = (Select distinct(name)
from studento)

[Delerna]

Hi Yogesh
But all of those records that you show do have a distinct name and address

[Delerna]

also this query

select roll from studento where name = (Select distinct(name)
from studento)
 

should generate a "subquery returned more than 1 value" error
because there are 2 distinct names

[ck9663]

I want to select that rollnos which have distinct name & address.
create table studento(roll int,name varchar,address varchar(5))
insert into studento values(1,'A','ADD1')
insert into studento values(2,'B','ADD2')
insert into studento values(3,'A','ADD3')
insert into studento values(4,'A','ADDR3')
select name from studento

I have tried the foll. query to retrive the roll that has distinct name,But query is still returning all roll numbers.

select roll from studento where name = (Select distinct(name)
from studento)

In your example, which roll will you choose for name A?

If you're choosing the first one, try this:

select name,min(roll) as roll from studento
group by name

You may also take the name column from the SELECT list.

-- CK