using STRING_AGG and the LAST_VALUE function in same request-CodePudding

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

CodePudding user response：

You can use sub-query

SELECT 
    STRING_AGG(t.name,'-') WITHIN GROUP (ORDER BY id) as names,
    STRING_AGG(t.[numerToCall],'-') WITHIN GROUP (ORDER BY id) as calledNumbers,
    --LAST_VALUE([numerToCall])) OVER (ORDER BY id) as lastCalled
    (SELECT TOP 1 InTbl.numerToCall FROM SimpleTest1 InTbl ORDER BY id desc) as lastCalled
FROM 
    SimpleTest1 t
GO

Or you can parse the result of the aggregation and get the result from the string without the need of another query (no sub-query)

SELECT 
    STRING_AGG(t.name,'-') WITHIN GROUP (ORDER BY id) as names,
    STRING_AGG(t.[numerToCall],'-') WITHIN GROUP (ORDER BY id) as calledNumbers,
    --LAST_VALUE([numerToCall])) OVER (ORDER BY id) as lastCalled
    RIGHT(STRING_AGG(t.[numerToCall],'-') WITHIN GROUP (ORDER BY id), CHARINDEX('-',REVERSE(STRING_AGG(t.[numerToCall],'-') WITHIN GROUP (ORDER BY id)))-1) as lastCalled
FROM 
    SimpleTest1 t
GO

Check both solutions in your specific database and choose the one which provide you better performance (SSMS execution Plan analyze shows that second query is better but don't count on it and check IO and TIME in your server)

CodePudding user response：

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;