SELECT DISTINCT ON, ordered by another column Please consider the following table test:

CREATE TABLE test(col1 int, col2 varchar, col3 date);
INSERT INTO test VALUES
  (1,'abc','2015-09-10')
, (1,'abc','2015-09-11')
, (2,'xyz','2015-09-12')
, (2,'xyz','2015-09-13')
, (3,'tcs','2015-01-15')
, (3,'tcs','2015-01-18');


postgres=# select * from test;
  col1 | col2 |    col3    
 ------+------+------------
     1 | abc  | 2015-09-10
     1 | abc  | 2015-09-11
     2 | xyz  | 2015-09-12
     2 | xyz  | 2015-09-13
     3 | tcs  | 2015-01-15
     3 | tcs  | 2015-01-18


I'd like to have a returned set ordered by date desc:

 col1 | col2 |    col3    
------+------+------------
    2 | xyz  | 2015-09-13
    1 | abc  | 2015-09-11
    3 | tcs  | 2015-01-18


What I've managed to accomplish with distinct on:

select distinct on (col1) col1, col2, col3 from test order by col1, col3 desc;
 col1 | col2 |    col3    
------+------+------------
    1 | abc  | 2015-09-11
    2 | xyz  | 2015-09-13
    3 | tcs  | 2015-01-18


And not what I need with having:

select distinct on (col1) col1, col2, col3 from test group by col1, col2, col3 having col3 = max(col3)
 col1 | col2 |    col3    
------+------+------------
    1 | abc  | 2015-09-10
    2 | xyz  | 2015-09-13
    3 | tcs  | 2015-01-18
This is a classic greatest-n-per-group problem. They frequently arise in a whole host of areas and, like Analytic functions (see below) are well worth studying.

Nowadays, it is typically solved by using Analytic (aka Window) functions - see the fiddle here.

You can use this query -

WITH cte AS
(
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS rn,
    col1, col2, col3 
  FROM test
  ORDER BY col3 DESC
)
SELECT * FROM cte 
WHERE rn = 1


Result -

rn  col1    col2    col3
1   2   xyz     2015-09-13
1   1   abc     2015-09-11
1   3   tcs     2015-01-18


Analytic functions are well worth getting to know - they are very powerful and you will find that they will repay you many times  for any effort you put into learning them. Run the inner query on its own - experiment, it's how I learnt. BTW, it's always worth tagging your question with the version of PostgreSQL that you are using!

A more traditional method of doing this would be

SELECT x, y, mc FROM
(
  SELECT col1 AS x, col2 AS y, MAX(col3) AS mc
  FROM test
  GROUP BY col1, col2
) AS tab
ORDER BY mc


Same result - also on the fiddle.