Post reply

Return first record

  • July 13, 2005 at 1:58 pm

    #90159

    Hi,

    I'm kind of stump, my problem is returning the first record of each address.  For example,

    record 1 contains

    id address1 address2 address3 address4

    1  Acme

  • July 13, 2005 at 2:07 pm

    #574113

    Can you normalise that system?

    Can you post more details about the data you can access and the data you need to extract?

  • July 13, 2005 at 2:31 pm

    #574132

    That's weird the rest of the message truncated.

    The gist of it I have two or more records that make up a complete address (don't ask why this was design this way) however using the addressnumber I can identify that that is the main part of the address.  What I want to do is only retrieve the first record using id that makes up the address.

    For example:

    uniqueidaddressnumberAdd1Add2Add3Add4Country Code
    100899ACMEOPERATIONS1000 ACCESS ROAD              SOMEwhere usaUS
    1008919           PO                                                                                                               

    I want to only retrieve the first record based on the addressnumber

  • July 13, 2005 at 2:39 pm

    #574138

    How come uniqueid is not unique??? Is it a fk of some sort?

  • July 13, 2005 at 2:44 pm

    #574142

    UniqueID is mislabel, and yes it's("UniqueID") a FK.  For the sake of extracting the first occurence of the id, I did not put in the PK.

    Thanks,

  • July 13, 2005 at 2:52 pm

    #574148

    Well the real pk of that table is priceless for that query :

    Select * from dbo.MainTable M inner join dbo.Adresses A on M.UniqueId = A.UniqueId and A.RealPk = (Select min(RealPk) as FirstAdresse from dbo.Adresses A2 where A2.Uniqueid = A.Uniqueid)

    Btw you should really add flags on that table that would tell you which is adress is the main adress, which one is secondary... (at lest for the main adress, it would simply take another condition in the join... and IsMainAdress = 1).

  • July 13, 2005 at 7:18 pm

    #574216

    Thanks Remi,

    I was trying to use the table the way it was loaded.  There is no documentation on the methodology on why the data was loaded in this fashion.  However I was thinking along the same lines as you are and creating a flag stating the main address record and rebuilding a new table off the original address table.  I believe I have enough data elements to build a main address criteria and load that into the new table.  Use the new address table instead of the original address table.  I hope that made sense.

    Thanks,

    Joe

  • July 13, 2005 at 9:35 pm

    #574236

    Yup, this is a case where the many tables needs to have some sort of order .

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply