Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor
  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Retrieving the lastest instance of a record [ SOLVED]

Retrieving the lastest instance of a record [ SOLVED]

Scheduled Pinned Locked Moved Database
questiondatabase
18 Posts 3 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • realJSOPR realJSOP

    I had to do something like this: create temp table select into temp table using group by select from permanent AD temp table for desired result Here's the whole this

    if object_id('tempdb..#DUAL')is not null
    DROP TABLE #DUAL

    create table #DUAL
    (
    ID int,
    datevalue datetime
    )

    insert into #DUAL (ID, datevalue)
    select id, max(datevalue)
    from metrics
    group by ID

    SELECT
    a.ID
    ,a.itemType
    ,a.Title
    ,a.Description
    ,m.DateValue
    FROM tableA a, metrics m, #Dual d
    WHERE a.id = d.id AND a.ID = m.ID AND m.datevalue = d.datevalue

    drop table #DUAL

    .45 ACP - because shooting twice is just silly
    -----
    "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
    -----
    "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

    I Offline
    I Offline
    i j russell
    wrote on last edited by
    #9

    SELECT a.Id,
    a.ItemType,
    a.Title,
    a.Description,
    m.DateValue
    FROM TableA a
    LEFT JOIN
    (
    SELECT Id,
    MAX(DateValue) as DateValue
    FROM Metrics
    GROUP BY Id
    ) m ON m.Id = a.Id

    ; If you only want Ids that have metrics then you can remove the LEFT to make it an inner join.

    realJSOPR 2 Replies Last reply
    0
    • I i j russell

      SELECT a.Id,
      a.ItemType,
      a.Title,
      a.Description,
      m.DateValue
      FROM TableA a
      LEFT JOIN
      (
      SELECT Id,
      MAX(DateValue) as DateValue
      FROM Metrics
      GROUP BY Id
      ) m ON m.Id = a.Id

      ; If you only want Ids that have metrics then you can remove the LEFT to make it an inner join.

      realJSOPR Offline
      realJSOPR Offline
      realJSOP
      wrote on last edited by
      #10

      Actually, an ID won't be in the tables unless it has a metric. I want the data from both table a and table b, but I want only the latest data for each ID. I tried to adapt the code you posted, but It SME complains with <sarcasm> it's way to verbose <sarcasm> error message: Incorrect syntax near the keyword 'ON'. :)

      .45 ACP - because shooting twice is just silly
      -----
      "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
      -----
      "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

      I 1 Reply Last reply
      0
      • I i j russell

        SELECT a.Id,
        a.ItemType,
        a.Title,
        a.Description,
        m.DateValue
        FROM TableA a
        LEFT JOIN
        (
        SELECT Id,
        MAX(DateValue) as DateValue
        FROM Metrics
        GROUP BY Id
        ) m ON m.Id = a.Id

        ; If you only want Ids that have metrics then you can remove the LEFT to make it an inner join.

        realJSOPR Offline
        realJSOPR Offline
        realJSOP
        wrote on last edited by
        #11

        I simply can't get that to work. T-SQL is being as touchy as a ex-wife seeking more child support.

        .45 ACP - because shooting twice is just silly
        -----
        "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
        -----
        "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

        1 Reply Last reply
        0
        • realJSOPR realJSOP

          Actually, an ID won't be in the tables unless it has a metric. I want the data from both table a and table b, but I want only the latest data for each ID. I tried to adapt the code you posted, but It SME complains with <sarcasm> it's way to verbose <sarcasm> error message: Incorrect syntax near the keyword 'ON'. :)

          .45 ACP - because shooting twice is just silly
          -----
          "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
          -----
          "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

          I Offline
          I Offline
          i j russell
          wrote on last edited by
          #12

          Try running this;

          if object_id('tempdb..#TableA')is not null
          DROP TABLE #TableA
          go
          if object_id('tempdb..#Metrics')is not null
          DROP TABLE #Metrics
          go

          create table #TableA
          (
          Id int,
          ItemType char(1),
          Title varchar(50),
          Description varchar(100)
          )
          go

          create table #Metrics
          (
          ID int,
          TableAId int,
          datevalue datetime
          )
          go

          insert into #TableA (ID, ItemType, Title, Description) values (1, 'A', 'First Title', 'First Description');
          insert into #TableA (ID, ItemType, Title, Description) values (2, 'A', 'Second Title', 'Second Description');
          insert into #TableA (ID, ItemType, Title, Description) values (3, 'B', 'Third Title', 'Third Description');
          go

          insert into #Metrics (ID, TableAId, datevalue) values (1, 1, '2010-01-01');
          insert into #Metrics (ID, TableAId, datevalue) values (2, 1, '2010-01-02');
          insert into #Metrics (ID, TableAId, datevalue) values (3, 3, '2010-01-03');
          insert into #Metrics (ID, TableAId, datevalue) values (4, 2, '2010-01-04');
          insert into #Metrics (ID, TableAId, datevalue) values (5, 2, '2010-01-05');
          insert into #Metrics (ID, TableAId, datevalue) values (6, 3, '2010-01-06');
          insert into #Metrics (ID, TableAId, datevalue) values (7, 1, '2010-01-07');
          insert into #Metrics (ID, TableAId, datevalue) values (8, 2, '2010-01-08');
          insert into #Metrics (ID, TableAId, datevalue) values (9, 1, '2010-01-09');
          insert into #Metrics (ID, TableAId, datevalue) values (10, 3, '2010-01-10');
          insert into #Metrics (ID, TableAId, datevalue) values (11, 3, '2010-01-11');
          insert into #Metrics (ID, TableAId, datevalue) values (12, 1, '2010-01-12');
          go

          SELECT
          a.ID
          ,a.itemType
          ,a.Title
          ,a.Description
          ,m.DateValue
          FROM #TableA a
          left join
          (
          select tableaid,
          MAX(datevalue) as datevalue
          from #Metrics
          group by tableaid
          ) m on m.tableaid = a.id
          go

          drop table #Metrics
          go
          drop table #TableA
          go

          realJSOPR 1 Reply Last reply
          0
          • I i j russell

            Try running this;

            if object_id('tempdb..#TableA')is not null
            DROP TABLE #TableA
            go
            if object_id('tempdb..#Metrics')is not null
            DROP TABLE #Metrics
            go

            create table #TableA
            (
            Id int,
            ItemType char(1),
            Title varchar(50),
            Description varchar(100)
            )
            go

            create table #Metrics
            (
            ID int,
            TableAId int,
            datevalue datetime
            )
            go

            insert into #TableA (ID, ItemType, Title, Description) values (1, 'A', 'First Title', 'First Description');
            insert into #TableA (ID, ItemType, Title, Description) values (2, 'A', 'Second Title', 'Second Description');
            insert into #TableA (ID, ItemType, Title, Description) values (3, 'B', 'Third Title', 'Third Description');
            go

            insert into #Metrics (ID, TableAId, datevalue) values (1, 1, '2010-01-01');
            insert into #Metrics (ID, TableAId, datevalue) values (2, 1, '2010-01-02');
            insert into #Metrics (ID, TableAId, datevalue) values (3, 3, '2010-01-03');
            insert into #Metrics (ID, TableAId, datevalue) values (4, 2, '2010-01-04');
            insert into #Metrics (ID, TableAId, datevalue) values (5, 2, '2010-01-05');
            insert into #Metrics (ID, TableAId, datevalue) values (6, 3, '2010-01-06');
            insert into #Metrics (ID, TableAId, datevalue) values (7, 1, '2010-01-07');
            insert into #Metrics (ID, TableAId, datevalue) values (8, 2, '2010-01-08');
            insert into #Metrics (ID, TableAId, datevalue) values (9, 1, '2010-01-09');
            insert into #Metrics (ID, TableAId, datevalue) values (10, 3, '2010-01-10');
            insert into #Metrics (ID, TableAId, datevalue) values (11, 3, '2010-01-11');
            insert into #Metrics (ID, TableAId, datevalue) values (12, 1, '2010-01-12');
            go

            SELECT
            a.ID
            ,a.itemType
            ,a.Title
            ,a.Description
            ,m.DateValue
            FROM #TableA a
            left join
            (
            select tableaid,
            MAX(datevalue) as datevalue
            from #Metrics
            group by tableaid
            ) m on m.tableaid = a.id
            go

            drop table #Metrics
            go
            drop table #TableA
            go

            realJSOPR Offline
            realJSOPR Offline
            realJSOP
            wrote on last edited by
            #13

            Hmmm... that ran just fine.

            .45 ACP - because shooting twice is just silly
            -----
            "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
            -----
            "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

            I 1 Reply Last reply
            0
            • realJSOPR realJSOP

              Hmmm... that ran just fine.

              .45 ACP - because shooting twice is just silly
              -----
              "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
              -----
              "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

              I Offline
              I Offline
              i j russell
              wrote on last edited by
              #14

              Can you post the code that you have tried that gives the syntax error.

              realJSOPR 1 Reply Last reply
              0
              • I i j russell

                Can you post the code that you have tried that gives the syntax error.

                realJSOPR Offline
                realJSOPR Offline
                realJSOP
                wrote on last edited by
                #15

                /****** Object: Table [dbo].[ArticleMetrics] Script Date: 03/21/2010 17:20:15 ******/
                SET ANSI_NULLS ON
                GO
                SET QUOTED_IDENTIFIER ON
                GO
                CREATE TABLE [dbo].[ArticleMetrics](
                [ArticleID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                [Votes] [int] NOT NULL,
                [Bookmarks] [int] NOT NULL,
                [Downloads] [int] NOT NULL,
                [Popularity] [decimal](5, 2) NOT NULL,
                [Rating] [decimal](5, 2) NOT NULL,
                [LastUpdated] [smalldatetime] NOT NULL,
                [Views] [int] NOT NULL,
                [DateScraped] [datetime] NOT NULL
                ) ON [PRIMARY]
                GO
                ALTER TABLE [dbo].[ArticleMetrics] WITH CHECK ADD CONSTRAINT [FK_ArticleMetrics_Articles] FOREIGN KEY([ArticleID])
                REFERENCES [dbo].[Articles] ([ArticleID])
                GO
                ALTER TABLE [dbo].[ArticleMetrics] CHECK CONSTRAINT [FK_ArticleMetrics_Articles]
                USE [CPAM]
                GO

                /****** Object: Table [dbo].[Articles] Script Date: 03/21/2010 17:20:46 ******/
                SET ANSI_NULLS ON
                GO
                SET QUOTED_IDENTIFIER ON
                GO
                SET ANSI_PADDING ON
                GO
                CREATE TABLE [dbo].[Articles](
                [ArticleID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                [ArticleType] [int] NOT NULL,
                [Title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                [Description] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                [DatePosted] [smalldatetime] NOT NULL,
                [Active] [bit] NOT NULL,
                [LastScraped] [datetime] NOT NULL,
                CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED
                (
                [ArticleID] ASC
                )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
                ) ON [PRIMARY]
                GO
                SET ANSI_PADDING OFF

                Essentially, I want to combine the appropriate rows but I only want one record returned per ArticleID that represents the LAST record that was added (determined by MAX(ArticleMetrics.DateScraped)).

                .45 ACP - because shooting twice is just silly
                -----
                "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
                -----
                "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

                I 1 Reply Last reply
                0
                • realJSOPR realJSOP

                  /****** Object: Table [dbo].[ArticleMetrics] Script Date: 03/21/2010 17:20:15 ******/
                  SET ANSI_NULLS ON
                  GO
                  SET QUOTED_IDENTIFIER ON
                  GO
                  CREATE TABLE [dbo].[ArticleMetrics](
                  [ArticleID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                  [Votes] [int] NOT NULL,
                  [Bookmarks] [int] NOT NULL,
                  [Downloads] [int] NOT NULL,
                  [Popularity] [decimal](5, 2) NOT NULL,
                  [Rating] [decimal](5, 2) NOT NULL,
                  [LastUpdated] [smalldatetime] NOT NULL,
                  [Views] [int] NOT NULL,
                  [DateScraped] [datetime] NOT NULL
                  ) ON [PRIMARY]
                  GO
                  ALTER TABLE [dbo].[ArticleMetrics] WITH CHECK ADD CONSTRAINT [FK_ArticleMetrics_Articles] FOREIGN KEY([ArticleID])
                  REFERENCES [dbo].[Articles] ([ArticleID])
                  GO
                  ALTER TABLE [dbo].[ArticleMetrics] CHECK CONSTRAINT [FK_ArticleMetrics_Articles]
                  USE [CPAM]
                  GO

                  /****** Object: Table [dbo].[Articles] Script Date: 03/21/2010 17:20:46 ******/
                  SET ANSI_NULLS ON
                  GO
                  SET QUOTED_IDENTIFIER ON
                  GO
                  SET ANSI_PADDING ON
                  GO
                  CREATE TABLE [dbo].[Articles](
                  [ArticleID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                  [ArticleType] [int] NOT NULL,
                  [Title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                  [Description] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                  [DatePosted] [smalldatetime] NOT NULL,
                  [Active] [bit] NOT NULL,
                  [LastScraped] [datetime] NOT NULL,
                  CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED
                  (
                  [ArticleID] ASC
                  )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
                  ) ON [PRIMARY]
                  GO
                  SET ANSI_PADDING OFF

                  Essentially, I want to combine the appropriate rows but I only want one record returned per ArticleID that represents the LAST record that was added (determined by MAX(ArticleMetrics.DateScraped)).

                  .45 ACP - because shooting twice is just silly
                  -----
                  "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
                  -----
                  "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

                  I Offline
                  I Offline
                  i j russell
                  wrote on last edited by
                  #16

                  I think that this is what you need;

                  SELECT a.*,
                  lm.*
                  FROM dbo.Articles AS a
                  CROSS APPLY
                  (SELECT TOP 1 m.*
                  FROM dbo.ArticleMetrics AS m
                  WHERE m.ArticleId = a.ArticleId
                  ORDER BY m.DateScraped DESC) AS lm

                  I would also add a compound primary key to ArticleMetrics on ArticleId, DateScraped.

                  realJSOPR 1 Reply Last reply
                  0
                  • I i j russell

                    I think that this is what you need;

                    SELECT a.*,
                    lm.*
                    FROM dbo.Articles AS a
                    CROSS APPLY
                    (SELECT TOP 1 m.*
                    FROM dbo.ArticleMetrics AS m
                    WHERE m.ArticleId = a.ArticleId
                    ORDER BY m.DateScraped DESC) AS lm

                    I would also add a compound primary key to ArticleMetrics on ArticleId, DateScraped.

                    realJSOPR Offline
                    realJSOPR Offline
                    realJSOP
                    wrote on last edited by
                    #17

                    But doesn't a primary key have to be unique? If that's the case, I can't make the DateScraped column a primary key. BTW, I've never even heard of "Cross Apply" before. :)

                    .45 ACP - because shooting twice is just silly
                    -----
                    "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
                    -----
                    "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

                    I 1 Reply Last reply
                    0
                    • realJSOPR realJSOP

                      But doesn't a primary key have to be unique? If that's the case, I can't make the DateScraped column a primary key. BTW, I've never even heard of "Cross Apply" before. :)

                      .45 ACP - because shooting twice is just silly
                      -----
                      "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
                      -----
                      "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

                      I Offline
                      I Offline
                      i j russell
                      wrote on last edited by
                      #18

                      Yes PKs do have to be unique, but I was assuming that the combination of ArticleId and DateScrapped would be unique so you could include them in a compound primary key. Otherwise, you should add a identity field to uniquely identify each row. Cross Apply and Outer Apply came in with Sql Server 2005. They are great for situations like 'Show me the last 5 orders per customer' that were very difficult before with ansi sql.

                      1 Reply Last reply
                      0
                      Reply
                      • Reply as topic
                      Log in to reply
                      • Oldest to Newest
                      • Newest to Oldest
                      • Most Votes

                      • Login
                      • Don't have an account? Register
                      • Login or register to search.
                      • First post
                        Last post
                      0
                      • Categories
                      • Recent
                      • Tags
                      • Popular
                      • World
                      • Users
                      • Groups