Problem with unique SQL query

I want to select all records, but have the query only return a single record per Product Name. My table looks similar to:

SellId   ProductName Comment
1          Cake        dasd 
2          Cake        dasdasd
3          Bread       dasdasdd  

where the Product Name is not unique. I want the query to return a single record per ProductName with results like:

SellId   ProductName Comment
1          Cake        dasd 
3          Bread       dasdasdd 

I have tried this query,

Select distict ProductName,Comment ,SellId from TBL#Sells

but it is returning multiple records wi开发者_Python百科th the same ProductName. My table is not realy as simple as this, this is just a sample. What is the solution? Is it clear?

Select  ProductName, 
min(Comment) , min(SellId) from TBL#Sells
group by ProductName

If y ou only want one record per productname, you ofcourse have to choose what value you want for the other fields.

If you aggregate (using group by) you can choose an aggregate function, htat's a function that takes a list of values and return only one : here I have chosen MIN : that is the smallest walue for each field.

NOTE : comment and sellid can come from different records, since MIN is taken...

Othter aggregates you might find useful :

FIRST : first record encountered
LAST : last record encoutered
AVG : average 
COUNT : number of records

first/last have the advantage that all fields are from the same record.

SELECT S.ProductName, S.Comment, S.SellId
FROM
   Sells S
   JOIN (SELECT MAX(SellId)
        FROM Sells
        GROUP BY ProductName) AS TopSell ON TopSell.SellId = S.SellId

This will get the latest comment as your selected comment assuming that SellId is an auto-incremented identity that goes up.

I know, you've got an answer already, I'd like to offer a way that was fastest in terms of performance for me, in a similar situation. I'm assuming that SellId is Primary Key and identity. You'd want an index on ProductName for best performance.

select 
    Sells.* 
from 
(
    select 
        distinct ProductName 
    from 
        Sells
) x
join 
    Sells 
on 
    Sells.ProductName = x.ProductName
    and Sells.SellId =
    (
        select 
            top 1 s2.SellId 
        from 
            Sells s2 
        where 
            x.ProductName = s2.ProductName 
        Order By SellId
    )

A slower method, (but still better than Group By and MIN on a long char column) is this:

select 
    * 
from
(
    select 
        *,ROW_NUMBER() over (PARTITION BY ProductName order by SellId) OccurenceId 
    from sells
) x
where 
    OccurenceId = 1

An advantage of this one is that it's much easier to read.

create table Sale
(
    SaleId int not null
        constraint PK_Sale primary key,
    ProductName varchar(100) not null,
    Comment varchar(100) not null
)

insert Sale
values
    (1, 'Cake', 'dasd'),
    (2, 'Cake', 'dasdasd'),
    (3, 'Bread', 'dasdasdd')

-- Option #1 with over()
select *
from Sale
where SaleId in
(
    select SaleId
    from
    (
        select SaleId, row_number() over(partition by ProductName order by SaleId) RowNumber
        from Sale
    ) tt
    where RowNumber = 1
)
order by SaleId

-- Option #2
select *
from Sale
where SaleId in
(
    select min(SaleId)
    from Sale
    group by ProductName
)       
order by SaleId

drop table Sale