By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,362 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,362 IT Pros & Developers. It's quick & easy.

Retrieve distinct records on the basis of other columns

P: 40
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
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
Hi Yogesh
But all of those records that you show do have a distinct name and address
Apr 11 '08 #2

Delerna
Expert 100+
P: 1,134
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
Expert 2.5K+
P: 2,878
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.