How to get last column in SQL?

user_15128981 0
Viewed 139

For example I have a table with data:

Column1  Column2   Column3 ...  Column1xx

1        a          10            x

2        b          20            y
 
3        c          30            z

What SQL query would get me only the last column?

Column1xx

x

y

z

Note: Column Name and Index of the last column is unknown

snowflake-cloud-data-platform sql
3 Answers

Fill your_table_schema and your_table_name. Originated from the post https://stackoverflow.com/a/38993544/18396231

SELECT COLUMN_NAME, ORDINAL_POSITION
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '{your_table_schema}'
AND TABLE_NAME ='{your_table_name}'
ORDER BY ORDINAL_POSITION DESC
LIMIT 1;
user_183962310

create so test data:

CREATE TABLE test.test.testo(aa number, bb number, cc number);

INSERT INTO test.test.testo values (1,10,100),(2,20,200),(3,30,300);

use tasozgurcem11 SQL to show we can get the column name:

SELECT COLUMN_NAME
FROM test.information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'TEST'
AND TABLE_NAME ='TESTO'
ORDER BY ORDINAL_POSITION DESC
LIMIT 1;

and the put that into Snowflake Scripting to having it run dynamically:

DECLARE
   query STRING;
   col_name STRING;
   res RESULTSET;
BEGIN
    LET _database := 'TEST';
    LET _table := 'TESTO';
    LET _schema := 'TEST';
    
    SELECT COLUMN_NAME INTO :col_name
        FROM test.information_schema.COLUMNS
        WHERE TABLE_SCHEMA = :_schema
        AND TABLE_NAME = :_table
        ORDER BY ORDINAL_POSITION DESC
        LIMIT 1;

    QUERY := 'SELECT '|| :col_name ||' FROM '|| _database ||'.'|| _schema ||'.'|| _table ||';';
                 
    res := (EXECUTE IMMEDIATE :QUERY);
    
    return table(res);
END;

gives:

CC
100
200
300
user_439920

this may help you where a function returns the last column name.

create or replace TABLE TEST_TABLE (
    ID NUMBER(38,0),
    C1 NUMBER(38,0),
    C2 VARCHAR(16777216) MASKING POLICY TEST_HK.PUBLIC.MASK_DATA,
    C3 DATE
);
CREATE OR REPLACE function LST_COLUMN(tab_nam varchar)
RETURNS VARCHAR
AS  'select column_name from information_schema.columns where table_name = tab_nam and ORDINAL_POSITION
        = (SELECT max(ORDINAL_POSITION) from information_schema.columns where table_name = tab_nam)';

select LST_COLUMN('TEST_TABLE') from test_table;
user_112279190
Related