CODEWITHSUNDEEP
sqlsql-serversql-server-2008
st_jonni
st_jonni

Reputation: 23

SQL Server : select one line from two lines with the same identifier

I have a table in SQL Server which contains more than 100 rows.

Below is an example of the data I want to get:

51206 69975314 InWork NULL
52160 70064509 Close  2018-05-10 12:52:49.000
52109 70071378 InWork NULL

I need if id contains 2 different status, select status InWork, or id in maximum time.

CREATE TABLE [dbo].[#RESULT_TABLE_X]
(
    [ID_ADD] NVARCHAR(50) NULL,
    [ID] NVARCHAR(50) NULL,
    [STATUS] NVARCHAR(50) NULL,
    [TIME] DATETIME NULL
) ON [PRIMARY]

INSERT INTO [dbo].[#RESULT_TABLE_X] 
VALUES ('51206', '69975314', 'InWork', NULL),
       ('52160', '70064509', 'Close', '2018-10-05 10:52:49.000'),
       ('52160', '70064509', 'Close', '2018-10-05 12:52:49.000'),
       ('52109', '70071378', 'InWork', NULL),
       ('51887', '70071378', 'Close', '2018-10-06 10:52:49.000')

I used the query below, but it does not display the required result for me:

SELECT
    ID_ADD, ID, STATUS,TIME
FROM
    [dbo].[#RESULT_TABLE_X]
WHERE
    STATUS NOT IN ('Close')
ORDER BY
    STATUS, ID

how solve this problem?

Upvotes: 2

Views: 997

Answers (2)

Sahi
Sahi

Reputation: 1484

try this:

    CREATE TABLE [dbo].[#RESULT_TABLE_X]
    (
        [ID_ADD] NVARCHAR(50) NULL,
        [ID] NVARCHAR(50) NULL,
        [STATUS] NVARCHAR(50) NULL,
        [TIME] DATETIME NULL
    ) ON [PRIMARY]

    INSERT INTO [dbo].[#RESULT_TABLE_X] 
    VALUES ('51206', '69975314', 'InWork', NULL),
           ('52160', '70064509', 'Close', '2018-10-05 10:52:49.000'),
           ('52160', '70064509', 'Close', '2018-10-05 12:52:49.000'),
           ('52109', '70071378', 'InWork', NULL),
           ('51887', '70071378', 'Close', '2018-10-06 10:52:49.000')

    ;with cte
    As(

        Select Max(ID_Add) as ID_ADD, [ID] as ID
        from [dbo].[#RESULT_TABLE_X] 
        Group by ID
    )

    Select r.ID_ADD,r.Id,MAX(Status) as Status, Max(Time) as Time from cte c
    INNER JOIN [dbo].[#RESULT_TABLE_X] r on c.ID_ADD=r.ID_ADD and c.Id=r.Id
    Group by r.Id ,r.ID_Add

    Drop Table [dbo].[#RESULT_TABLE_X]

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

make use of ROW_NUBMER() to generate running number per ID_ADD & ID 

SELECT *
FROM
(
    SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY ID_ADD, ID 
                                          ORDER BY TIME DESC)
    FROM   #RESULT_TABLE_X
) D
WHERE D.RN = 1

Upvotes: 1

Related Questions