4 Replies Latest reply: Aug 8, 2012 11:28 AM by Rahul Lakhina RSS

    Load only Top 1 record ordered by date

      Hi All,

       

      The below table has New User column set to 'Y' for the very first serialnumber that every user registers. With this we can get how many new users registered our products. However the requirement is to get how many new companys registered our products.So for that I need the second table (NewCustomer) as shown below...

       

      Existing Table (RegistrationTemp)
      Serial NumberRegistrationDateNew UserUserNameCompanyName
      11/1/2011YU1A
      21/10/2011YU2B
      31/19/2011NU1A
      41/28/2011YU3A
      52/6/2011YU4C
      62/15/2011NU4C
      72/24/2011YU5B
      83/5/2011NU5B
      93/14/2011YU6B
      103/23/2011YU7C
      114/1/2011NU1A
      124/10/2011YU8D
      134/19/2011NU8D
      144/28/2011YU9E
      155/7/2011YU10E
      Required Table (NewCustomer)
      Serial NumberRegistrationDateNew CompanyCompany name
      11/1/2011YA
      21/10/2011YB
      52/6/2011YC
      124/10/2011YD
      144/28/2011YE

       

      I would need something as below ...

       

      NewCustomer:

      LOAD

      Top1 serialnumber,
      'Yes' AS NewCustomer
      RESIDENT
      RegistrationTemp WHERE NewUser = 'Yes'
      GROUP BY CompanyName

      ORDER By RegistrationDate ASC;

       

      Will wait for your inputs.

       

      Thanks

      Shilpa