I have a table containing post adresses. my query looks like this:
SELECT "ID", postcode, numbertype, minnumber, maxnumber
INTO huidigPostcodeID_FK, databasePostcode, databaseNumberType, databaseMinNumber, databaseMaxNumber
FROM POSTCODE
WHERE POSTCODE_ID = 79417568 AND (NUMBERTYPE = 'odd' OR NUMBERTYPE = 'mixed')
In most cases this works fine since it has to deliver 1 row only. in the case when there are 2 identical recors (one with numbertype = odd and the other mixed) the query returns 2 rows.
ID POSTCODE NUMBERTYPE MINNUMBER MAXNUMBER
---------- -------- ---------- ---------- ----------
395755 7941KD odd 9 9
395756 7941KD mixed 1 22
I don't want this because I get an error by this. Has somebody a solution wherein the programm only gets one record with numbertype = 'mixed' in it?
So in the above example I only wan't this record to show up:
ID POSTCODE NUMBERTYPE MINNUMBER MAXNUMBER
395756 7941KD mixed 1 22
I think one might also use a window function to accomplish this:
SELECT "ID", postcode, numbertype, minnumber, maxnumber
INTO huidigPostcodeID_FK, databasePostcode, databaseNumberType, databaseMinNumber, databaseMaxNumber
FROM (
SELECT "ID", postcode, numbertype, minnumber, maxnumber
, ROW_NUMBER() OVER ( PARTITION BY postcode ORDER BY DECODE(numbertype, 'mixed', 0, 1) ) AS rn
FROM postcode
WHERE postcode_id = 79417568 AND (numbertype = 'odd' OR numbertype = 'mixed')
) WHERE rn = 1
In this case, if there are two records for a given value of postcode
, it will select the one where the value of numbertype
is 'mixed'
. One might also simply use ORDER BY numbertype
in the OVER( )
clause but that's not as explicit in saying that 'mixed'
ought to come first.
Hope this helps.