469,275 Members | 1,796 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

Retrieve distinct records on the basis of other columns

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)
Apr 11 '08 #1
3 1583
Delerna
1,134 Expert 1GB
Hi Yogesh
But all of those records that you show do have a distinct name and address
Apr 11 '08 #2
Delerna
1,134 Expert 1GB
also this query
Expand|Select|Wrap|Line Numbers
  1. select roll from studento where name = (Select distinct(name)
  2. from studento)
  3.  
should generate a "subquery returned more than 1 value" error
because there are 2 distinct names
Apr 11 '08 #3
ck9663
2,878 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. select name,min(roll) as roll from studento
  2. group by name
You may also take the name column from the SELECT list.

-- CK
Apr 11 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Florian | last post: by
8 posts views Thread by Rich | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.