SQL DISTINCT Value 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 ma开发者_如何学Gony 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.
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;
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')
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
SELECT DISTINCT x.*,fruit FROM peopleFruit pf
CROSS APPLY
(SELECT TOP 1 firstname, lastname FROM peopleFruit pf1 WHERE pf.fruit=pf1.fruit) x
精彩评论