Select MAX(DateTime) returning multiple lines

Question

I'm trying to select the last MAX(DateTime) status from the table "Zee" but if the DateTime is the same it returns two lines, and I would like to get only the last one (maybe last inserted?).

here is the query:

SELECT Z."ID" AS ID,Z."A" AS A,Z."B" AS B,Z."C" AS C,Z."D" AS D
FROM ZEE Z
INNER JOIN
    (SELECT ID, A, B, MAX(C) AS C
    FROM ZEE
    GROUP BY A, B) groupedtt
ON Z.A = groupedtt.A
AND Z.B = groupedtt.B
AND Z.C = groupedtt.C
WHERE (
        Z.B = 103
     OR Z.B = 104
);

and the result:

Thanks,

Regards.

May be `ORDER BY ID DESC` and `LIMIT 1` in main query work. I am talking about MySQL syntax. — Jignesh M. Khatri, Sep 08 '17 at 13:18
Duplicate of https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column (just grouping by more columns) — MT0, Sep 08 '17 at 13:36

score 1 · Accepted Answer · answered Sep 08 '17 at 13:31

1

I usually use rank() for such things:

select Z."ID" AS ID,Z."A" AS A,Z."B" AS B,Z."C" AS C,Z."D" AS D
from (select Z.*, rank()over(partition by A,B order by C desc, rownum) r from ZEE Z
)Z where Z.r=1

answered Sep 08 '17 at 13:31

wolfrevokcats

2,100
1
12
12

1

Why are you using `RANK` with `ROWNUM` instead of just using `ROW_NUMBER`? – MT0 Sep 08 '17 at 13:33
Well, I guess, it's a habit. Of course, ROW_NUMBER can be used instead of rank. – wolfrevokcats Sep 08 '17 at 13:42

score 1 · Answer 2 · answered Sep 08 '17 at 13:31

Use the ROW_NUMBER() analytic function (you will also eliminate the self-join):

SELECT ID, A, B, C, D
FROM (
  SELECT ID,
         A,
         B,
         C,
         D,
         ROW_NUMBER() OVER ( PARTITION BY A, B ORDER BY C DESC ) As rn
  FROM   ZEE
)
WHERE   rn = 1;

Select MAX(DateTime) returning multiple lines

2 Answers2