SQL to Return First Two Columns of a Table

SQL to return first two columns of a table

You have to get the column names first. Most platforms support this:

select column_name,ordinal_position
  from information_schema.columns
 where table_schema = ...
   and table_name = ...
   and ordinal_position <= 2

Select data from a table where only the first two columns are distinct

A select distinct will be based on all columns so it does not guarantee the first two to be distinct

select pk1, pk2, '11', max(c1), max(c2), max(c3) 
from table 
group by pk1, pk2 

SQL Server : select first occurrence from two columns

Apply row_number() to the union of home and away teams and join to the table:

select m.id, m.home_team, m.away_team, m.date,
  max(case t.rn when 1 then case homeaway when 1 then 1500 end end) home_elo,
  max(case t.rn when 1 then case homeaway when 2 then 1500 end end) away_elo
from matches m inner join (
  select u.*,
    row_number() over (partition by team order by date) rn
  from (
    select 1 homeaway, home_team team, date from matches
    union all
    select 2, away_team, date from matches
  ) u
) t on t.date = m.date and t.team in (m.home_team, m.away_team)
group by m.id, m.home_team, m.away_team, m.date
order by m.date, m.home_team, m.away_team

See the demo.

Results:

> id | home_team | away_team | date       | home_elo | away_elo
> -: | :-------- | :-------- | :--------- | -------: | -------:
>  3 | Joker     | Rovaniemi | 10.09.1999 |     1500 |     1500
>  4 | TeePee    | Rovaniemi | 11.09.1999 |     1500 |     
>  2 | Pori      | Turku     | 12.09.1999 |     1500 |     1500
>  6 | Rovaniemi | Joker     | 12.09.1999 |          |     
>  1 | Turku     | Haka      | 13.09.1999 |          |     1500
>  5 | Joker     | TeePee    | 20.09.1999 |          |     

How do I stack the first two columns of a table into a single column?

You need unpivot:

SELECT Id, Code, Other
FROM 
   Source
UNPIVOT
   (Code FOR code_ IN 
      (code_1, code_2)
) AS unpvt;

See the fiddle here.

How to SELECT FROM two tables and match two columns in SQL?

You can try below way -

SELECT a.id,a.project,a.element,b.floorid FROM dbo.IMP_ELEMENT a
            INNER JOIN dbo.IMP_MODEL_GEOMETRY b ON a.Project = b.Project and a.element=b.element
WHERE a.Project LIKE '%$objNr%'

SQL Query to get first two highest column values from a table

If the highest column is salary, you do this:

select salary from employee
order by salary desc
limit 2

You would do the same for any other columns, just replace the column name in the SELECT and in the ORDER BY sections.

select first N columns of MySQL table

Please have a look at Bill Karwin's answer first. But if you know how to order your column names there could be a solution that makes use of a dynamic query.

To select all column names from a table, you can use a query like this:

SELECT `column_name` 
FROM   `information_schema`.`columns` 
WHERE  `table_schema`=DATABASE() 
       AND `table_name`='yourtablename';

(please have a look at this answer). And making use of GROUP_CONCAT:

GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY column_name)

we can return all column names in a single row, separated by commas:

`col1`, `col2`, `col3`, ...

(I also added quotes around the name of the column, and please notice that we have to order our list of columns somehow, otherwise there are no guarantees about the order in which column names are returned).

Then we can cut the returned string, using SUBSTRING_INDEX, in order to get, for example, the first 2 column names:

SUBSTRING_INDEX(columns, ',', 2)

and our final query, that concatenates 'SELECT ', the selected columns above, and ' FROM Tab1', and inserts the resulting string into the @sql variable is this:

SELECT
  CONCAT(
    'SELECT ',
    SUBSTRING_INDEX(
      GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY column_name),
      ',',
      2),
    ' FROM Tab1'
  )
FROM
  information_schema.columns 
WHERE
  table_schema=DATABASE() 
  AND table_name='Tab1'
INTO @sql;

It's value will be something like this:

@sql = "SELECT `col1`, `col2` FROM Tab1"

and you can then prepare your statement, and execute it:

PREPARE stmt FROM @sql;
EXECUTE stmt;

Please see fiddle here.

---

Related Topics