CODEWITHSUNDEEP
sqlsql-serverleft-joindistinct
Ali Sheikhpour
Ali Sheikhpour

Reputation: 11096

select distinct id when using left join and order by on two tables

I have two tables and I am going to get distinct id from first one based on a complex order by clauses on both tables. I'm confused how to use distinct or groub by because both of them prevent using order by on other columns.

My code using distinct:

select distinct(shops.id) from shops 
left join expiration on shops.id=expiration.shopid 
order by 
  shops.grade desc,
  expiration.startdate asc,
  expiration.enddate desc,
  shops.id

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

My code using group by:

select max(shops.id) as shopid from shops 
left join expiration on shops.id=expiration.shopid
group by shopid
order by 
  shops.grade desc,
  expiration.startdate asc,
  expiration.enddate desc,
  shops.id

Column "shops.grade" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 1

Views: 3632

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726929

You can use aggregate functions in ORDER BY:

SELECT shops.id AS shopid -- No need to use MAX(...) here
FROM shops 
LEFT JOIN expiration ON shops.id=expiration.shopid
GROUP BY shopid
ORDER BY
  MAX(shops.grade) DESC,
  MIN(expiration.startdate) ASC,
  MAX(expiration.enddate) DESC,
  shops.id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If I understand correctly, you have a prioritization query. If so, you can handle it with row_number() not select distinct or group by:

select se.shopid
from (select s.id as shopid,
             row_number() over (partition by s.id
                                order by s.grade desc, e.startdate asc, e.enddate desc, s.id
                               ) as seqnum
      from shops s left join
           expiration e
           on s.id = e.shopid
     ) se
where seqnum = 1;

Upvotes: 0

Related Questions