I have a database of address info:
IDNUM ADDTYPE SEQNUM STATUS STREET1 etc
1 RS 1 123 Main
1 BU 2 456 Main
2 RS 1 I 789 Main
2 BU 2 987 Elm
and so on

Some have have active RS (residence) and BU (business) addresses,
some have only one or the other active.

I need to select only one active address per IDNUM, If they have an active RS I need that one, if not then I can use the active BU.

How can I select these without duplicating the records with both types active?

assume STATUS='I' means inactive (this was my best guess, based on your information)

That will do the trick.

Thank you very much.

I forget about using the max and min with anything but numbers.

How about this ...?

SELECT o.*
FROM ADD_INFO AS o
INNER JOIN ADD_INFO AS i
ON o.IDNUM = i.IDNUM
AND o.STATUS = i.STATUS
AND o.ADDTYPE > i.ADDTYPE

I think this will be faster if there are more records...

What do you think r937??

jay82, you are not testing for active status

plus, i think you will still need an outer join

You are right ... I don't think it's possible with even LEFT OUTER JOIN... but i am still trying...

Any problem with this query??

SELECT o.*, i.*
FROM ADD_INFO AS o
LEFT OUTER JOIN ADD_INFO AS i
ON o.IDNUM = i.IDNUM
AND o.ADDTYPE <> i.ADDTYPE
WHERE i.ADDTYPE IS NULL
OR o.ADDTYPE > i.ADDTYPE
AND o.STATUS <> 'I'

yes, a major problem

your ANDs and ORs need parentheses

plus, i don't think the status is being handled correctly

are you guessing, or testing?

because the best way to develop queries is to test them

Here's your parentheses ... and it's tested... but still if you are getting different result let me know... it will be good thing to learn... that's why we are here .. !

SELECT o.*, i.*
FROM ADD_INFO AS o
LEFT OUTER JOIN ADD_INFO AS i
ON o.IDNUM = i.IDNUM
AND o.ADDTYPE <> i.ADDTYPE
WHERE (i.ADDTYPE IS NULL
OR o.ADDTYPE > i.ADDTYPE)
AND o.STATUS <> 'I'

what about "If they have an active RS I need that one, if not then I can use the active BU."

you seem to be returning both of them

and you're not testing i.STATUS either