Select max date No result I want

Question

SELECT
mat.matid,
MAX (to_date(to_char (matdatetable.matdateupdate,'yyyy-mm-dd'),'yyyy-mm-dd')),
mat.matuserid,
mat.matname,    
mat.matprice    
FROM
matdatetable
LEFT JOIN mat ON matdatetable.sourceid = mat.matid

RESULT

matid   matdate update      matuserid    matname   matprice
-------------------------------------------------------------    
1       2012-01-01 0:0:0:0  0111-1       aaa       100
1       2012-08-01 0:0:0:0  0111-1       aaa       125
1       2013-08-30 0:0:0:0  0111-1       aaa       150
2       2012-01-01 0:0:0:0  0222-1       bbb       130
2       2012-08-21 0:0:0:0  0222-1       bbb       110
2       2013-07-30 0:0:0:0  0222-1       bbb       100
3       2012-01-01 0:0:0:0  0565-1       ccc       100
3       2013-09-30 0:0:0:0  0565-1       ccc       230

But I want to. Results

matid     matdate update    matuserid    matname   matprice
------------------------------------------------------------------
1       2013-08-30 0:0:0:0  0111-1       aaa       150
2       2013-07-30 0:0:0:0  0222-1       bbb       100
3       2013-09-30 0:0:0:0  0565-1       ccc       230

Next time, please provide your table definition, Postgres version and a definition of what you want exactly. — Erwin Brandstetter, Nov 07 '13 at 05:57

score 0 · Accepted Answer · edited May 23 '17 at 12:13

SELECT DISTINCT ON (1)
       t.sourceid AS matid
      ,t.matdateupdate::date AS matdate_update
      ,m.matuserid
      ,m.matname   
      ,m.matprice
FROM   matdatetable t
LEFT   JOIN mat m ON m.matid = t.sourceid
ORDER  BY 1, t.matdateupdate DESC;

Gives you the latest (according to matdateupdate) entry per sourceid. Your question isn't clear what you want exactly.

Using sourceid rather than matid, since you have a LEFT JOIN and matid could be NULL. Or your use of LEFT JOIN is incorrect ...

Explanation for DISTINCT ON in this related answer:
Select first row in each GROUP BY group?

t.matdateupdate::date casts your timestamp (assuming for lack of information) to date. That seems to be what you want. If you really need the redundant time 00:00, use datetrunc('day', t.matdateupdate) instead.

Select max date No result I want

1 Answers1