How to Select Nth Column in a Select Clause from a Table/View

Is there a way to select nth column in a SELECT clause from a table/view

If you are using MS SQL Server you can

sp_help ViewName

and then scroll to the 144th column in the Column_name result set to see what the name of that column is.

Additionally you can choose "Copy with Headers" in the result pane in SQL Server Management Studio and paste the result set into Excel with the headers (column names) intact.

How to select the nth column, and order columns' selection in BigQuery

You can get information from INFORMATION_SCHEMA.COLUMNS but you'll need to create a table or view from the CTE:

CREATE OR REPLACE VIEW `project.dataset.secondary_prep_view` as select * from (select 1 as id, "a" as name, "b" as value)

Then:

SELECT * FROM dataset.INFORMATION_SCHEMA.COLUMNS WHERE  table_name = 'secondary_prep_view';

How to select the nth row in a SQL database table?

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

Select nth column in database table

You can use a CTE with a ROW_NUMBER() function to achieve this:

;WITH CTE AS
(
   SELECT 
      Column1, Column2, Column3,
      (your list of additional columns - if needed),
      RN = ROW_NUMBER() OVER (ORDER BY InsertionDate)
)
SELECT 
    FirstValue = (SELECT Column1 FROM CTE WHERE RN = N),
    SecondValue = (SELECT Column2 FROM CTE WHERE RN = M),
    ThirdValue = (SELECT Column3 FROM CTE WHERE RN = K)

You need to replace the N, M, K with actual integer values in this query - or define SQL variables to hold those three values.

PostgreSQL: Refer to specific nth column of WITH created temponary table

Just give them a name, e.g as part of the WITH clause:

WITH temporary_table (c1, c2) AS (
    VALUES ('First', 1),
           ('Second', 2),
           ('Third', 3)
)
SELECT c2
FROM temporary_table;

A slightly more complicated way is to name the columns of the VALUES clause:

WITH temporary_table AS (
  select *
  from (
    VALUES ('First', 1),
           ('Second', 2),
           ('Third', 3)  
  ) as t(c1, c2)
)
SELECT c2
FROM temporary_table;

Selecting Nth Record in an SQL Query

This is a classic interview question.

In Ms SQL 2005+ you can use the ROW_NUMBER() keyword and have the Predicate ROW_NUMBER = n

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
)  

SELECT * 
FROM OrderedOrders 
WHERE RowNumber = 5;

In SQL2000 you could do something like

SELECT Top 1 *FROM
[tblApplications]
where [ApplicationID] In
(
    SELECT TOP 5 [ApplicationID]
    FROM [dbo].[tblApplications]
    order by applicationId Desc
)

How to select the nth column of a bigquery table with no aliases?

You can cast as a struct with a name for the column:

SELECT CAST(t AS STRUCT<f0_ INT64, f1_ INT64, f2_ INT64>).f0_
FROM (SELECT 1, 2, 3) AS t

SQL: How to select 1st, 3rd, 11th and nth row from a table?

If there is a primary key defined for the table that is an integer based data type--both MySQL and SQLite have auto_increment for example--then you can use:

SELECT t.*
  FROM TABLE t
 WHERE t.id IN (1,3, 11)

...where id is the auto_increment column.

There's very little detail to go on, but MySQL and SQLite do not have analytic query support, making queries rather complicated:

SELECT y.*
  FROM (SELECT t.*,
                        (SELECT COUNT(*)
                            FROM TABLE x
                           WHERE x.col <= t.col) AS rank
               FROM TABLE t) y
 WHERE y.rank IN (1, 3, 11)

SQL SELECT and check column from second table

To select all the data from tableA where id is not available in tableB you don't need join rather you can use not exists or not in.

I would prefer not exists

Using not exists:

SELECT * from tableA a
WHERE NOT EXISTS (
                    SELECT 1
                    FROM tableB b
                    WHERE b.user_id = a.id
                 );

using not in:

SELECT * from tableA a
WHERE id not in (SELECT user_id FROM tableB );

DB-Fiddle:

 create table TableA (id int, name varchar(50), lastname varchar(50));
 insert into TableA values(1,'john','smith');
 insert into TableA values(2,'Paul','smith');
 
 create table TableB (id int, user_id int, something varchar(50));
 insert into TableB values(1,2,'bla');
 insert into TableB values(2,3,'bla');

Query: (using not exists)

 SELECT * from tableA a
 WHERE NOT EXISTS (
                     SELECT 1
                     FROM tableB b
                     WHERE b.user_id = a.id
                  );

Output:

---