I have the following table INPUT:

And I need to keep the first event for each ID and its TYPE in a OUTPUT table:

I have tried to use a group by construct :

create OUTPUT as
select ID, min(DATE) as DATE, TYPE
from INPUT
group by ID

But I got: not a group by expression from the TYPE field.

How to keep the good value for the TYPEfield?

CodePudding user response：

See if you can use this as a template

drop table if exists #have;

create table #have
(
  ID     [int]
, date   [datetime]
, type   [varchar](10)
)
;

insert into #have
values
  (884, '2017-03-16 06:08:40', 'B')
, (857, '2017-03-24 07:14:29', 'A')
, (857, '2017-06-24 12:15:29', 'A')
, (884, '2017-10-05 00:33:08', 'A')
, (255, '2019-08-02 02:47:22', 'B')
;

select * from #have;

SELECT a.*     
FROM #have a inner join
(
    SELECT id, MIN(date) AS date
    FROM #have
    GROUP BY id
) b ON a.id = b.id and a.date = b.date

CodePudding user response：

To keep the correct value for the TYPE field in the output table, you can use the FIRST_VALUE function to select the first value for the TYPE field for each ID group. Here is an example of how you could use the FIRST_VALUE function in your query:

CREATE OUTPUT AS
SELECT
  ID,
  MIN(DATE) AS DATE,
  FIRST_VALUE(TYPE) OVER (PARTITION BY ID ORDER BY DATE) AS TYPE
FROM INPUT
GROUP BY ID

In this query, the FIRST_VALUE function is used with the OVER clause to select the first value for the TYPE field for each ID group, as determined by the PARTITION BY and ORDER BY clauses in the OVER clause. The FIRST_VALUE function returns the first value of TYPE for each ID group, which is the value that you want to keep in the output table.