Re: please help me in building a SQL query...........

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Fri, 07 Jan 2005 10:41:27 +0100
Message-ID: <crllg7$45p$1@news.BelWue.DE>

techimadhu wrote:
> I have two tables
>
> 1. Person2Address Table
> Columns - Personuid, addressuid
> there is no primary key for this table.
>
> 2. Address Table
> Columns - addressuid(which is a guid), addresstext, validitystartdate
>
> typical data in the tables are like this
>
> person2Address table
> -----------------------
> personuid addressuid
> ------------------------
> 1 4
> 1 5
> 2 6
> 3 7
> 3 8
>
> Address Table
> ---------------------------------------------
> addressuid addresstext validitystartdate
> ---------------------------------------------
> 4 abcd 01-09-82
> 5 xyz 01-10-95
> 6 pqrs 08-09-78
> 7 mnop 04-08-80
> 8 huil 04-08-80
>
> I want each persons addresstext which is having the latest validity
> start date.
> if for a person two addresses have same validity start period then i
> have to show any one of that.
>
> ie the result should be
>
> personuid addresstext
> -------------------------
> 1 xyz
> 2 pqrs
> 3 mnop
>
> i need to write a single query to achive this.
>
> note: addressuid is actually not numeric, it is actually alphanumeric
> and not generated in sequential order.
>

Try this:

  select personuid, addresstext, max(validitystartdate) , rn   from (

      select 	a.addressuid,
		addresstext,
		validitystartdate,
		personuid,
		row_number() over (partition by personuid order by  validitystartdate) rn
      from addresstable a, person2address p
      where p.addressuid = a.addressuid)

  where rn = 1
  group by personuid, addresstext, rn

  PERSONUID ADDRESSTEXT                    MAX(VALID         RN
---------- ------------------------------ --------- ----------
          1 abcd                           01-SEP-82          1
          2 pqrs                           08-SEP-78          1
          3 mnop                           04-AUG-80          1

Since you don't mention a version I assume you're at least at 9i, else row_number won't be available.

HTH
Holger Received on Fri Jan 07 2005 - 03:41:27 CST