How to eliminate a row which having the maximum ID in sql query?

Question

I need to Eliminate final row which have the maximum ID from a sql query.How can I do it? I tries following query.But it didn't generated records

SELECT a.DocEntry, a.Project, a.DocTotal, a.U_Installement
FROM OPCH AS a
LEFT JOIN PCH1 AS b
    ON b.DocEntry = a.DocEntry
LEFT JOIN OPOR AS c
    ON c.DocEntry = b.BaseEntry
    AND a.Project = c.Project
WHERE a.CANCELED = 'N'
AND a.Project = c.Project
GROUP BY a.DocEntry, a.Project, a.DocTotal, a.U_Installement
HAVING MAX(a.DocEntry) <> a.DocEntry;

in which table you actually has this id, i mean your table name and column name — DarkRob, Jul 22 '19 at 09:55
Why do you have a `LEFT JOIN` to `OPOR ` when you turn it into an implicit inner join in your `WHERE`? Also [Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3). — Thom A, Jul 22 '19 at 10:02
The argument is in this post too https://stackoverflow.com/questions/2854257/sql-how-to-select-a-row-having-a-column-with-max-value — Biagio, Jul 22 '19 at 10:09

Renat · Answer 1 · 2019-07-22T10:03:33.913

2

Looks like this would work:

SELECT a.DocEntry, a.Project, a.DocTotal, a.U_Installement
FROM OPCH AS a
  JOIN PCH1 AS b
    ON b.DocEntry = a.DocEntry
  JOIN OPOR AS c
    ON c.DocEntry = b.BaseEntry
    AND a.Project = c.Project
WHERE a.CANCELED = 'N'
    AND a.DocEntry NOT IN (SELECT MAX(DocEntry) FROM OPCH)

edited Jul 22 '19 at 10:03

answered Jul 22 '19 at 10:02

Renat

7,718
2
20
34

1

Might as well move `a.Project = c.Project` into the `ON` and change the 2 joins to an `INNER JOIN` too; leaving the `JOIN` to `OPOR` as a `LEFT JOIN` and not changing it to an `INNER JOIN` will change the queries behaviour when you move the clause to the `ON`. – Thom A Jul 22 '19 at 10:03
@Larnu, thank you, that's right, I've fixed my answer – Renat Jul 22 '19 at 10:06
1

@Renat . . . This will *not* work, because the maximum entry might be filtered out by other conditions. – Gordon Linoff Jul 22 '19 at 10:39

Zaynul Abadin Tuhin · Answer 2 · 2019-07-22T10:10:24.137

2

it seems to me you want to eliminate each group max id and therefore i used row_number()

    with cte as
    (

    SELECT a.DocEntry, a.Project, a.DocTotal, a.U_Installement,
     row_number()over(partitioned by a.Project, a.DocTotal, a.U_Installement
      ordere by a.DocEntry desc) rn
    FROM OPCH AS a
    JOIN PCH1 AS b
        ON b.DocEntry = a.DocEntry
     JOIN OPOR AS c
        ON c.DocEntry = b.BaseEntry
        AND a.Project = c.Project
    WHERE a.CANCELED = 'N'

 ) select * from cte where rn<>1

edited Jul 22 '19 at 10:10

answered Jul 22 '19 at 10:08

Zaynul Abadin Tuhin

31,407
5
33
63

The join to `PCH1` in an implied `INNER JOIN` too, due to the clause `c.DocEntry = b.BaseEntry` in the `ON` of `OPOR`. There has to be a row from `OPOR` according to the logic in the OP's query, so might as well just make them both an `INNER JOIN`. – Thom A Jul 22 '19 at 10:09

score 0 · Answer 3 · answered Jul 22 '19 at 10:49

First, learn to use meaningful table aliases rather than arbitrary letters. This makes queries much easier to read.

Second, your WHERE clause turns the outer joins in to inner joins.

Then, you are removing duplicates -- but these are probably generated by the JOINs. That can be fixed by using EXISTS.

So for your full query:

SELECT o.DocEntry, o.Project, o.DocTotal, o.U_Installement
FROM OPCH o
WHERE o.CANCELED = 'N' AND
      EXISTS (SELECT 1
              FROM PCH1 p JOIN
                   OPOR op
                   ON op.DocEntry = p.BaseEntry
              WHERE p.DocEntry = o.DocEntry AND
                    op.Project = o.Project
             );

Then, you can incorporate window functions into this:

SELECT DocEntry, Project, DocTotal, U_Installement
FROM (SELECT o.DocEntry, o.Project, o.DocTotal, o.U_Installement,
             MAX(o.DocEntry) OVER () as max_DocEntry
      FROM OPCH o
      WHERE o.CANCELED = 'N' AND
            EXISTS (SELECT 1
                    FROM PCH1 p JOIN
                         OPOR op
                         ON op.DocEntry = p.BaseEntry
                    WHERE p.DocEntry = o.DocEntry AND
                          op.Project = o.Project
                   )
     ) o
WHERE docEntry < max_docEntry;

score 0 · Answer 4 · answered Jul 22 '19 at 12:27

We use this method regually:

select a.* from ( 
    SELECT a.DocEntry, a.Project, a.DocTotal, a.U_Installement
    FROM OPCH a 
    GROUP BY a.DocEntry, a.Project, a.DocTotal, a.U_Installement
    HAVING MAX(a.DocEntry) <> a.DocEntry 
) AS a 
LEFT JOIN PCH1 AS b
    ON b.DocEntry = a.DocEntry
LEFT JOIN OPOR AS c
    ON c.DocEntry = b.BaseEntry
    AND a.Project = c.Project
WHERE a.CANCELED = 'N'

How to eliminate a row which having the maximum ID in sql query?

4 Answers4