Loading ...
Sorry, an error occurred while loading the content.

Get specifc records from a table

Expand Messages
  • pdhakeph
    Hi, I have table as follows Row Id, fld_1, Date, fld_2, fld_3 fld_1 will be used to join to other table. Multiple records can have same value for fld_1. The
    Message 1 of 5 , Jul 7, 2004
    • 0 Attachment
      Hi,

      I have table as follows

      Row Id, fld_1, Date, fld_2, fld_3

      fld_1 will be used to join to other table. Multiple records
      can have same value for fld_1. The 'Date' field will always have
      different value.

      What I need is collection of records (all fields) for each unique
      value of fld_1, where 'Date' is maximum.

      e.g.

      Row Id, fld_1, Date, fld_2, fld_3
      1, A, 2/2/04, p, q
      2, A, 2/3/04, z, v
      3, A, 2/4/04, t, y
      4, B, 1/3/04, s, d
      5, C, 3/3/04, T, h
      6, C, 5/6/04, y, u

      I should get following set
      3, A, 2/4/04, t, y
      4, B, 1/3/04, s, d
      6, C, 5/6/04, y, u

      What would be query ?

      Thanks and regards,

      Prasanna Dhakephalkar
    • Razvan Socol
      ... SELECT * FROM yourtable a WHERE date= (SELECT MAX(date) FROM yourtable b WHERE a.fld_1=b.fld_1) Razvan
      Message 2 of 5 , Jul 7, 2004
      • 0 Attachment
        Prasanna Dhakephalkar wrote:
        > [...]
        > What I need is collection of records (all fields) for each unique
        > value of fld_1, where 'Date' is maximum.
        > [...]

        SELECT * FROM yourtable a WHERE date=
        (SELECT MAX(date) FROM yourtable b WHERE a.fld_1=b.fld_1)

        Razvan
      • Michael Gerholdt
        select a.field1, a.field2, b.field3 from tableA a join tableB b on b.fld_1 = a.fld_1 where a.date = (select max(c.Date) from tableA c where c.fld_1 = a.fld_1)
        Message 3 of 5 , Jul 7, 2004
        • 0 Attachment
          select a.field1, a.field2, b.field3
          from tableA a join tableB b on b.fld_1 = a.fld_1
          where a.date = (select max(c.Date)
          from tableA c
          where c.fld_1 = a.fld_1)




          > Hi,
          >
          > I have table as follows
          >
          > Row Id, fld_1, Date, fld_2, fld_3
          >
          > fld_1 will be used to join to other table. Multiple records
          > can have same value for fld_1. The 'Date' field will always have
          > different value.
          >
          > What I need is collection of records (all fields) for each unique
          > value of fld_1, where 'Date' is maximum.
          >
          > e.g.
          >
          > Row Id, fld_1, Date, fld_2, fld_3
          > 1, A, 2/2/04, p, q
          > 2, A, 2/3/04, z, v
          > 3, A, 2/4/04, t, y
          > 4, B, 1/3/04, s, d
          > 5, C, 3/3/04, T, h
          > 6, C, 5/6/04, y, u
          >
          > I should get following set
          > 3, A, 2/4/04, t, y
          > 4, B, 1/3/04, s, d
          > 6, C, 5/6/04, y, u
          >
          > What would be query ?
          >
        • pdhakeph
          Hi, Thanks ,Michael Gerholdt, Razvan Socol Regards, Prasanna.
          Message 4 of 5 , Jul 7, 2004
          • 0 Attachment
            Hi,

            Thanks ,Michael Gerholdt, Razvan Socol

            Regards,

            Prasanna.
          • Balmukund Lakhani
            Hi Prasanna Dhakephalkar, below query may help you set nocount on if exists ( select X from sysobjects where type = U and name = SQL_QNC ) begin drop
            Message 5 of 5 , Jul 8, 2004
            • 0 Attachment
              Hi Prasanna Dhakephalkar,

              below query may help you

              set nocount on
              if exists ( select 'X'
              from sysobjects
              where type = 'U'
              and name = 'SQL_QNC')
              begin
              drop table SQL_QNC
              end
              go
              Create table SQL_QNC(
              RowId int,
              fld_1 Varchar(10),
              Date datetime,
              fld_2 Varchar(10),
              fld_3 Varchar(10))
              go
              insert into SQL_QNC
              values(1, 'A', '02-Feb-2004', 'fld_21', 'fld_31')
              insert into SQL_QNC
              values(2, 'A', '02-Mar-2004', 'fld_22', 'fld_32')
              insert into SQL_QNC
              values(3, 'A', '02-Apr-2004', 'fld_23', 'fld_33')
              insert into SQL_QNC
              values(4, 'B', '01-Mar-2004', 'fld_24', 'fld_34')
              insert into SQL_QNC
              values(5, 'C', '03-Mar-2004', 'fld_25', 'fld_35')
              insert into SQL_QNC
              values(6, 'C', '03-Jun-2004', 'fld_26', 'fld_36')
              go
              SELECT * FROM SQL_QNC
              select outer_table.RowId,
              outer_table.fld_1,
              outer_table.Date,
              outer_table.fld_2,
              outer_table.fld_3
              from SQL_QNC outer_table,
              (select fld_1 'FLD_1_INNER',
              max(Date) 'DATE_INNER'
              from SQL_QNC
              group by fld_1) A
              where A.DATE_INNER = outer_table.date
              and A.FLD_1_INNER = outer_table.fld_1
              ORDER BY 1




              set nocount off



              ---------------------
              Date: Wed, 07 Jul 2004 15:05:06 -0000
              From: "pdhakeph" <pdhakeph@...>
              Subject: Get specifc records from a table

              Hi,

              I have table as follows

              Row Id, fld_1, Date, fld_2, fld_3

              fld_1 will be used to join to other table. Multiple records
              can have same value for fld_1. The 'Date' field will always have
              different value.

              What I need is collection of records (all fields) for each unique
              value of fld_1, where 'Date' is maximum.

              e.g.

              Row Id, fld_1, Date, fld_2, fld_3
              1, A, 2/2/04, p, q
              2, A, 2/3/04, z, v
              3, A, 2/4/04, t, y
              4, B, 1/3/04, s, d
              5, C, 3/3/04, T, h
              6, C, 5/6/04, y, u

              I should get following set
              3, A, 2/4/04, t, y
              4, B, 1/3/04, s, d
              6, C, 5/6/04, y, u

              What would be query ?

              Thanks and regards,

              Prasanna Dhakephalkar
            Your message has been successfully submitted and would be delivered to recipients shortly.