HelloWorld HelloWorld - 7 months ago 14
SQL Question

Display the latest row only

Goal:

If you retrieve any duplicate data that is data in the column secondid, then you to retrieve one row only from the latest date. For instance in the data below I have two different datetime, I would like to retrieve the data '2016-05-02 07:34:14.377' from value 6 in column secondid.

Problem:

I code seems not to be working and what am I missing.

Info:

*There are many data in and you cannot hard code the value in in the code.

Thank you!

CREATE TABLE [dbo].[testing2](
[id] [int] NOT NULL,
[secondid] [int] NULL,
[value] [varchar](30) NULL,
[category] [int] NULL,
[test_id] [int] NULL,
[id_type] [int] NOT NULL,
[Testing2Datetime] [datetime] not NULL,
CONSTRAINT [PK_testing2] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


INSERT INTO [test].[dbo].[testing2]
VALUES (3, 3, 'a' ,2 ,11 ,1, '2016-05-01 07:34:14.377');

INSERT INTO [test].[dbo].[testing2]
VALUES (4, 4, 'a' ,2 ,11 ,1, '2016-05-01 07:34:14.377');

INSERT INTO [test].[dbo].[testing2]
VALUES (5, 5, 'a' ,2 ,11 ,0, '2016-05-01 07:34:14.377');

INSERT INTO [test].[dbo].[testing2]
VALUES (6, 6, 'a' ,2 ,11 ,2, '2016-05-01 07:34:14.377');

INSERT INTO [test].[dbo].[testing2]
VALUES (7, 6, 'a' ,2 ,11 ,2, '2016-05-02 07:34:14.377');



select
bb.secondid,
max(bb.Testing2Datetime)
from [dbo].[testing2] bb
group by
bb.secondid,
bb.Testing2Datetime


enter image description here

Answer

The maximum value of Testing2Datetime per Testing2Datetime is the Testing2Datetime itself. You should remove it from the group by clause and retrieve the maximum value per secondid only:

select
    bb.secondid,
    max(bb.Testing2Datetime)
from [dbo].[testing2] bb
group by
    bb.secondid -- Here!
Comments