CODEWITHSUNDEEP
sqlsql-serverodbckepserverex
Jheferson
Jheferson

Reputation: 71

Select bottom n records and concatenate in same row

I have a select n bottom records in table and return a concatenate values in same row. This is code is ok but not return n bottom records:

SELECT
    STUFF((
        SELECT '; ' + 
            ISNULL(Val1, '') + '; ' +
            ISNULL(Val2, '') + '; ' +
            ISNULL(Val3, '') + '; ' +
            ISNULL(Val4), '')
        FROM Table_x
    FOR XML PATH ('')), 1, 2, '') AS val;

I writed other query return n bottom recods but I need to use KEPServerEX with ODBC conections and it only support SELECT and EXECUTE:

DECLARE @max_id INT
SELECT @max_id = MAX(id) FROM table_x
SET @max_id = @max_id - 20
SELECT
    STUFF((
        SELECT '; ' + 
            ISNULL(val1, '') + '; ' +
            ISNULL(val2, '') + '; ' +
            ISNULL(val3, '') + '; ' +
            ISNULL(val4, 14), '')
        FROM 
        (
            SELECT *
            FROM table_x
            WHERE id > @max_id
        ) AS Latest_rec
    FOR XML PATH ('')), 1, 2, '') AS val;

I need to have a long word of the return. Ex.: val1;val2 ; val3; val4; val1; val2; val3; val4.

I use Microsoft SQL Server 2012 (SP3) - 11.0.6020.0 (X64) Express Edition (64-bit). But the problem is KEPServerEX which only accepts SELECT and EXECUTE.

enter image description here

In moment, i not find other query return n bottom records in same row supported by KEPServerEX.

Upvotes: 1

Views: 104

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

You can have bottom n rows by using row_number() window ranking in descending order of id column. I have selected bottom 5 rows. You can choose as you wish.

SELECT
    STUFF((
        SELECT '; ' + 
            ISNULL(Val1, '') + '; ' +
            ISNULL(Val2, '') + '; ' +
            ISNULL(Val3, '') + '; ' +
            ISNULL(Val4, ''), '')
        (select *, row_number()over(order by id desc) rn FROM Table_x)t
        where rn=5
    FOR XML PATH ('')), 1, 2, '') AS val;

If you are using SQL Server 2017 or higher then you can use string_agg() which is by far easy to use and faster.

SELECT string_agg(ISNULL(Val1, '') + '; ' + ISNULL(Val2, '') + '; ' + ISNULL(Val3, '') + '; ' + ISNULL(Val4,''), ';')
from (select *, row_number()over(order by id desc) rn FROM Table_x)t
where rn=5

Upvotes: 1

Serg
Serg

Reputation: 22811

Select last 20 rows when ordered by id. Return in ASC order, with a single query

SELECT
    STUFF((
        SELECT '; ' + 
            ISNULL(val1, '') + '; ' +
            ISNULL(val2, '') + '; ' +
            ISNULL(val3, '') + '; ' +
            ISNULL(val4, 14), '')
        FROM 
        (
            SELECT top(20) *
            FROM table_x
            ORDER BY id DESC
        ) AS Latest_rec
        ORDER BY id ASC
        FOR XML PATH ('')), 1, 2, '') AS val;

Upvotes: 1

Related Questions