Get max url from PostgreSQL statement I have a very complex query which generates a view in PostgreSQL. 
Now the query returns duplicated rows, well almost duplicated: one field, the "url" column is different. 

Now we still want to group those "almost duplicated" rows. We can do this by skipping the url column, but we need that column. We already got the latest date by using this statement:

max("cdata".date) AS date


So we would like to do the same thing, but with url. So get the latest row by date and show that url. 

Is this possible? 

Like I said, the query is complex, so creating a subquery which executes the same query again with some changes wouldn't be a good idea. Also I don't have a primary key, so that makes it even more difficult. I hope you guys have some kind of idea to fix this issue.

The current query:

CREATE MATERIALIZED VIEW cdataview AS 
 SELECT md5(ROW("cdata".acc_id, "cdata".adgcid, "cdata".adgid, "cdata".kid, "cdata".sterm, "cdata".mtype)::text) AS id,
    "cdata".acc_id,
    "cdata".kword,
    "cdata".kid,
    "cdata".url,
    "cdata".adgcid,
    "cdata".adgname,
    "cdata".adgid,
    "cdata".adgstatus,
    "cdata".sterm,
    "cdata".mtype,
    sum("cdata".cost) AS total_cost,
    sum("cdata".cval) AS cval,
    sum("cdata".impr) AS impr,
    sum("cdata".clicks) AS clicks,
    sum("cdata".convcl) AS convcl,
    ( SELECT accs.name
           FROM accs
          WHERE accs.id = "cdata".acc_id) AS accname,
    ( SELECT cmps.name
           FROM cmps
          WHERE cmps.id = "cdata".adgcid) AS campname,
    ( SELECT kpr.qscore
           FROM kpr
          WHERE kpr.id = "cdata".kid
          ORDER BY kpr.date DESC
         LIMIT 1) AS qscore,
    round(
        CASE
            WHEN sum("cdata".impr) = 0 THEN 0::numeric
            ELSE sum("cdata".clicks)::numeric / sum("cdata".impr)::numeric
        END, 3) AS ctr,
    round(
        CASE
            WHEN sum("cdata".clicks) = 0 THEN 0::numeric
            ELSE sum("cdata".cost) / sum("cdata".clicks)::numeric
        END, 3) AS cpc,
    round(
        CASE
            WHEN sum("cdata".convcl) = 0::numeric THEN 0::numeric
            ELSE sum("cdata".cost) / sum("cdata".convcl)
        END, 3) AS cpa,
    round(
        CASE
            WHEN sum("cdata".impr) = 0 THEN 0::numeric
            ELSE sum("cdata".impr::numeric * "cdata".avgpos) / sum("cdata".impr)::numeric
        END, 2) AS avgpos,
    max("cdata".date) AS date
   FROM sqpr "cdata"
     LEFT JOIN kpr kpr ON "cdata".kid = kpr.id AND kpr.date = (( SELECT max(kpr.date) AS max
           FROM kpr
          WHERE kpr.id = "cdata".kid))
  GROUP BY "cdata".kword, "cdata".kid, "cdata".acc_id, "cdata".adgstatus, "cdata".adgcid, "cdata".adgname, "cdata".adgid, "cdata".sterm, "cdata".mtype, "cdata".url
Guess I have figured it out.

By first selecting the first url:

select min("cdata".url) as url


And then order the query by date:

order by date asc


It does return the right url and it combines the rest of the columns.

EDIT:
Above answer did not work as expected. This didn't either: https://wiki.postgresql.org/wiki/First/last_(aggregate)

EDIT 2:
Alright, I ran several tests and this seems to go well:

last("cdata".url) AS url


in combination with the max(date). So if someone has a better solution, please let me know :)

EDIT 3:
Alright, I think I just fixed it. I just select the url by executing a subquery and select the right row by comparing all "group by" fields, guess this is the best solution to my problem:

(SELECT url from sqpr as cd where"cd".kword = "cdata".kword and cd.kid="cdata".kid and cd.accid="cdata".accid and cd. adgstatus ="cdata". adgstatus and cd. adgcid ="cdata". adgcid and cd. adgname ="cdata". adgname and cd. adgid ="cdata". adgid and cd. sterm ="cdata". sterm and cd. mtype ="cdata". mtype ORDER BY date DESC LIMIT 1) AS url