In a table occasions is stored with start\_date, end\_date and with sort\_order and other fields.

I am working on a **django-rest-framework(rest-api)**, so that i am using custom **postgres** query for this.

My need is sorting these results acc. to

1. sort categories
2. top 6 upcoming occasions acc. date range (occasion\_start\_date, Occasion\_end\_date)
3. rest other occasion list acc. to sort order or name

for this i make a query which is not working:

[sql query which gives error because orderby clause is not used with UNION](http://i.stack.imgur.com/fFNiB.png)

[this query return results but upcoming occasion not on top :-(](http://i.stack.imgur.com/nfraQ.png)

My First query is:

```
SELECT * FROM public.product_productcategory WHERE is_active=True and occasion_end_date > now() and occasion_start_date > now()
UNION 
SELECT * FROM public.product_productcategory WHERE is_active=True  
ORDER BY sort_order, name asc

```

this query not give my exact result

```
SELECT * FROM public.product_productcategory WHERE is_active=True and occasion_end_date > now() and occasion_start_date > now() ORDER BY occasion_start_date asc
UNION 
SELECT * FROM public.product_productcategory WHERE is_active=True  ORDER BY sort_order, name asc

```

This gives error.