CODEWITHSUNDEEP
sqlsql-server
Amasian21
Amasian21

Reputation: 55

Selecting specific distinct column in SQL

I am trying to create a select statement so that it does a specific distinct on one column. I am trying to make it so that there is not multiple fruits within each id. If there is multiple fruits under an id, I would like use only 1 approved fruit, over the rotten fruit. If there is only 1 fruit under that id, use it.

SELECT id, fruit, fruitweight, status 
  FROM myfruits


Raw data from current select    

    id |  fruit  | fruitweight | status
    1  |  apple  |      .2     | approved
    1  |  apple  |      .8     | approved
    1  |  apple  |      .1     | rotten
    1  |  orange |      .5     | approved
    2  |  grape  |      .1     | rotten
    2  |  orange |      .7     | approved
    2  |  orange |      .5     | approved

How it should be formatted after constraint

    id |  fruit  | fruitweight | status
    1  |  apple  |      .2     | approved
    1  |  orange |      .5     | approved
    2  |  grape  |      .1     | rotten
    2  |  orange |      .7     | approved

I can do something along the lines of select distinct id,fruit,fruitweight,status from myfruits, but that will only take out the duplicates if all columns are the same.

Upvotes: 3

Views: 86

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82010

Another option is using the WITH TIES clause.

Example

Select top 1 with ties *
 From  YourTable 
 Order By Row_Number() over (Partition By id,fruit order by status,fruitweight)

Upvotes: 3

Raphael Müllner
Raphael Müllner

Reputation: 438

A shorter version of scsimon's solution without aggregates. If you have SQL Server < 2012, you'll have to use case instead of iif.

select
    id
    ,fruit
    ,fruitweight
    ,status
from 
(
    select
        id
        ,fruit
        ,fruitweight
        ,status
        ,rownum         = row_number() over(partition by id, fruit order by iif(status = 'approved', 0, 1), fruitweight desc)
    from myfruits
) x
where rownum = 1

EDIT: I started writing before scsimon edited his post to included a version without aggregates...

Upvotes: 1

S3S
S3S

Reputation: 25152

CTE with aggregate and row_number.

declare @YourTable table (id int, fruit varchar(64), fruitweight decimal(2,1),status varchar(64))
insert into @YourTable
values
(1,'apple',0.2,'approved'),
(1,'apple',0.8,'approved'),
(1,'apple',0.1,'rotten'),
(1,'orange',0.5,'approved'),
(2,'grape',0.1,'rotten'),
(2,'orange',0.7,'approved'),
(2,'orange',0.5,'approved')


;with cte as(
select
    id
    ,fruit
    ,fruitweight = min(fruitweight)
    ,[status]
    ,RN = row_number() over (partition by id, fruit order by case when status = 'approved' then 1 else 2 end)
from 
    @YourTable
group by
    id,fruit,status)

select
    id
    ,fruit
    ,fruitweight
    ,status
from
    cte
where RN = 1

Another method, without the aggregate... assuming you want the first fruightweight

;with cte as(
select
    id
    ,fruit
    ,fruitweight 
    ,[status]
    ,RN = row_number() over (partition by id, fruit order by case when status = 'approved' then 1 else 2 end, fruitweight)
from 
    @YourTable)

select
    id
    ,fruit
    ,fruitweight
    ,status
from
    cte
where RN = 1

Upvotes: 4

Related Questions