using STRING_AGG and the LAST_VALUE function in same request

Question

How do I get the 777777 from the last record found in the order by id

SELECT 
    STRING_AGG(name,'-') WITHIN GROUP (ORDER BY id) as names,
    STRING_AGG([numerToCall],'-') as calledNumbers,
    --LAST_VALUE([numerToCall])) OVER (ORDER BY id) as lastCalled
    '777777' as lastCalled
FROM 
    SimpleTest1

to create table and data

 CREATE TABLE [dbo].[simpleTest1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [numerToCall] [varchar](50) NOT NULL,
 CONSTRAINT [PK_simpleTest1] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    )
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[simpleTest1] ON 
INSERT [dbo].[simpleTest1] ([id], [name], [numerToCall]) VALUES (1, N'benny', N'555555')
INSERT [dbo].[simpleTest1] ([id], [name], [numerToCall]) VALUES (2, N'helle', N'999999')
INSERT [dbo].[simpleTest1] ([id], [name], [numerToCall]) VALUES (3, N'hans', N'777777')
SET IDENTITY_INSERT [dbo].[simpleTest1] OFF

The solution was

SELECT 
    STRING_AGG(t.name,'-') WITHIN GROUP (ORDER BY id) as names,
    STRING_AGG(t.numerToCall, '-') as calledNumbers,
    MIN(t.xxxx) as lastCalled
FROM (
    SELECT *,
      LAST_VALUE(t.numerToCall) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as xxxx
    from
    SimpleTest1 t
) t;

thanks @Charlieface

Answer 1

You can put the window function in a derived table, and then use an aggregation function on it (either MIN or MAX)

SELECT 
    STRING_AGG(t.name,'-') WITHIN GROUP (ORDER BY id) as names,
    STRING_AGG(t.numerToCall, '-') WITHIN GROUP (ORDER BY id) as calledNumbers,
    MIN(t.numerToCall]) as lastCalled
FROM (
    SELECT *,
      LAST_VALUE(t.numerToCall) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as lastCalled
    SimpleTest1 t
) t;