how to select only max(id) if you need less columns in group by

Question

The thing is that i could have values like

ID         STREET_ID    HOUSENUMBER POSTCODE
10000000   20512120     22              04114
11000000   20512120     22              04074

problem is that POSTCODE have to be in select, but i need distinct STREET_ID + HOUSENUMBER with MAX id, by example i just want to show 11000000,20512120,22,04074 out of 2 records because of MAX(h.ID).

this is my code

SELECT DISTINCT
    MAX(h.ID),
    h.street_id,
    h.houseNumber,
    h.postindex AS postCode
FROM house h
WHERE
    h.postindex IS NOT NULL AND
    h.STREET_ID IS NOT NULL
GROUP BY
    h.street_id,
    h.houseNumber
ORDER BY
    STREET_ID,
    CAST(REGEXP_REPLACE(REGEXP_REPLACE(h.houseNumber, '(\-|\/)(.*)'), '\D+') AS NUMBER),
    h.houseNumber

i have an error " ORA-00979: not a GROUP BY expression " and i understand it, because POSTCODE is not in GROUP BY, how to deal with that?

GMB · Answer 1 · 2019-12-09T10:21:12.470

0

Don't aggregate. Instead, you can filter with a correlated subquery:

select h.*
from house h
where id = (
    select max(h1.id) 
    from house h1 
    where h1.street_number = h.street_number and h1.house_number = h.house_number
)

I would expect this solution to be as efficient as it gets, especially with an index on (street_number, house_number, id).

edited Dec 09 '19 at 10:21

answered Dec 09 '19 at 10:14

GMB

216,147
25
84
135

score 0 · Answer 2 · answered Dec 09 '19 at 10:16

You can do it with subquery and exists:

SELECT * 
  FROM house h
 WHERE NOT EXISTS (SELECT 1 FROM house h2
                    WHERE h2.street_id   = h.street_id
                      AND h2.houseNumber = h.houseNumber
                      AND h2.id > h.id)

Tim Biegeleisen · Accepted Answer · 2019-12-09T10:32:21.273

0

Your requirement is a good candidate for ROW_NUMBER:

WITH cte AS (
    SELECT h.*,
        ROW_NUMBER() OVER (PARTITION BY h.STREET_ID, h.HOUSENUMBER ORDER BY h.ID DESC) rn
    FROM house h
)

SELECT ID, STREET_ID, HOUSENUMBER, POSTCODE
FROM cte
WHERE rn = 1;

An index on (STREET_ID, HOUSENUMBER, ID) might speed up the above query, because it would let Oracle quickly find the max ID record for each street/house number.

edited Dec 09 '19 at 10:32

answered Dec 09 '19 at 10:16

Tim Biegeleisen

502,043
27
286
360

is it faster than using subquery? – German Varanytsya Dec 09 '19 at 10:29
@GermanVaranytsya Well my answer actually _is_ using a subquery. You may compare the execution time against the GMB answer to see which is fastest. – Tim Biegeleisen Dec 09 '19 at 10:31

SRG · Answer 4 · 2019-12-09T11:09:47.607

-1

select distinct id,street_id,house_no,postal_code from house where id in ( 
SELECT MAX(id) from house group by street_id,house_no)

edited Dec 09 '19 at 11:09

answered Dec 09 '19 at 10:28

SRG

345
1
9

This will only work if the `id` column is unique. – MT0 Dec 09 '19 at 10:34
please see the edited query for distinct id – SRG Dec 09 '19 at 11:10
[db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=b6acd2ce34a2d06abeabfe7a531a08b6) Still does not work if you have duplicate `id` values across different `street_id`/`house` pairs. – MT0 Dec 09 '19 at 11:16

how to select only max(id) if you need less columns in group by

4 Answers4