MGCR7 MGCR7 - 1 month ago 9
SQL Question

Order by with a condition

I have two tables,

Products
and
Category
. each
product
is related to a specific
Category
, and has an
Expiry_Date
which may be
NULL
value.

I want to query all
products
ordered by the soonest
Expiry_Date
first. Null-Expiry_date products are ordered last for a
Category
with specific name, like
Food
.

UPDATED (sample data below):



Product table:

enter image description here

Category Table:

enter image description here

Results:

enter image description here

Answer Source

This isn't just about ordering, you want to exclude some rows with null dates but include others based on the category name; and then order what's left:

select p.prod_id, p.name, p.expiry_date, c.cat_id
from product p
join category c on c.cat_id = p.cat_id
where (c.name = 'Food' or p.expiry_date is not null)
order by p.expiry_date desc nulls last;

The where clause excludes products will null expiry dates, unless they are in the category called 'Food'. The order-by is then straightforward, though as you want it in descending date order you need to specify nulls last to get those... er... last.

Demo with your sample data in CTEs:

with product (prod_id, name, expiry_date, cat_id) as (
  select 1, 'NAME1', date '2018-01-10', 1 from dual
  union all select 2, 'NAME2', date '2018-01-11', 2 from dual
  union all select 3, 'NAME3', date '2018-01-12', 3 from dual
  union all select 4, 'NAME4', null, 1 from dual
  union all select 5, 'NAME5', null, 2 from dual
  union all select 6, 'NAME6', date '2018-01-13', 2 from dual
  union all select 7, 'NAME7', date '2018-01-14', 2 from dual
  union all select 8, 'NAME8', null, 3 from dual
),
category (cat_id, name) as (
  select 1, 'Food' from dual
  union all select 2, 'Food1' from dual
  union all select 3, 'Food2' from dual
)
select p.prod_id, p.name, p.expiry_date, c.cat_id
from product p
join category c on c.cat_id = p.cat_id
where (c.name = 'Food' or p.expiry_date is not null)
order by p.expiry_date desc nulls last;

   PROD_ID NAME  EXPIRY_DAT     CAT_ID
---------- ----- ---------- ----------
         7 NAME7 2018-01-14          2
         6 NAME6 2018-01-13          2
         3 NAME3 2018-01-12          3
         2 NAME2 2018-01-11          2
         1 NAME1 2018-01-10          1
         4 NAME4                     1