SQL DISTINCT Value Question

Question

How can I filter my results in a Query? example

I have 5 Records

John,Smith,apple
Jane,Doe,apple
Fred,James,apple
Bill,evans,orange
Willma,Jones,grape

Now I want a query that would bring me back 3 records with the DISTINCT FRUIT, BUT... and here is the tricky part, I still want the columns for First Name , Last Name. PS I do not care which of the 3 it returns mind you, but I need it to only return 3 (or what ever how many DISTINCT fruit there are.

ex return would be

John,Smith,apple
Bill,evans,orange
Willma,Jones,grape

Thanks in advance I've been banging my head on this all day.

Answer 1

Oddly enough, the best solution doesn't involve GROUP BY.

WITH DistinctFruit AS (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY Fruit ORDER BY LastName) AS FruitNo, 
        LastName,  
        FirstName, 
        Fruit 
    FROM table)
SELECT FirstName, LastName, Fruit
FROM  DistinctFruit
WHERE FruitNo = 1;

Answer 2

If you have a small amount of data (not tens of thousands of rows), you can do sub-queries.

select distinct t1.fruit as Fruit, 
    (select top 1 t2.lastname 
    from t1 as t2
    where t1.fruit = t2.fruit
    order by t2.lastname) as LastName,
    (select top 1 t2.firstname 
    from t1 as t2
    where t1.fruit = t2.fruit
    order by t2.lastname, t2.firstname) as FirstName
from t1

Note the FirstName column is sorted the same as the LastName column. This will give you a matching last name with the correct first name.

Here is my test data:

create table t1
(firstname varchar(20),
lastname varchar(20),
fruit varchar(20))


insert into t1
values ('John','Smith','apple')
insert into t1
values ('Jane','Doe','apple')
insert into t1
values ('Fred','James','apple')
insert into t1
values ('Bill','evans','orange')
insert into t1
values ('Willma','Jones','grape')

Answer 3

Just another solution

    select distinct x.*,fruit from t1 
    cross apply 
    (select top 1 firstname, lastname from t1 t2 where t1.fruit=t2.fruit) x

Answer 4

SELECT DISTINCT x.*,fruit FROM peopleFruit pf 
CROSS APPLY  
(SELECT TOP 1 firstname, lastname FROM peopleFruit pf1 WHERE pf.fruit=pf1.fruit) x