0

I'm trying to select specific values in an Access database. So the database contains many colums, I'm only able to select the first values, or last:

SELECT Column1, First(Column7) AS Column7_1, First(Column8) AS FirstOfColumn8, [Column1] & [Column7_1] AS [Column1&Column7]
FROM [Table1]
GROUP BY Column1;

Now what I'm trying to figure out is how to get the nth values in those columns. What would be perfect is if SQL would recognize third(Column7)... I tried the following:

SELECT Column1, First(Column7) AS Column7_1, First(Column8) AS FirstOfColumn8, [Column1] & [Column7_1] AS [Column1&Column7]
FROM [Table1]
WHERE Column7 > (SELECT First(Column7) FROM [Table1]) AND Column8 > SELECT First(Column8) FROM [Table1]) 
GROUP BY Column1;

But this is not getting me there. the values from the different columns do not correspond anymore. Any guess on how I could get this? Thanks

fredj59
  • 1
  • you have no `order by` on any of your showed querys. if `order by` is missing first(), third() or nth() will result in a meaningless value, that is because sql querys (also in access) do not guaranty order if it is not specified. side note: third() and nth() do not exists – Luis Siquot Nov 17 '14 at 13:27

2 Answers2

0

AFAIK, selecting a column by column number with the aforementioned syntax is not really supported with SQL.

Something similar to the following could do the trick (with dynamic SQL):

get list of column names in given table*
for each column name
  do this
end

*The below query could be of use!

SELECT s.NAME SchemaName, 
       t.NAME TableName, 
       c.NAME ColumnName 
FROM   sys.columns c 
       INNER JOIN sys.tables t 
               ON c.object_id = t.object_id 
       INNER JOIN sys.schemas s 
               ON t.schema_id = s.schema_id
Drew
  • 2,417
  • 4
  • 34
  • 51
0

thanks for the replies, what I forgot to specify is that I'm actually not trying the nth cell in a column; it's more about getting the nth value contained in a specific colmn. For example, take the following table

Column1  Column2  Column3
A1       Prop1     20
A1       Prop1     20
A2       Prop2     15
A2       Prop1     20
A3       Prop2     15
A3       Prop3     5 
A3       Prop3     5 
A4       Prop1     20
A4       Prop2     15
A4       Prop3     5
A4       Prop4     10

I would therefore like to select the nth values from column 2 for each column1id, so that to get for first element:

Column1  Column2  Column3
A1       Prop1     20
A2       Prop1     20
A3       Prop2     15
A4       Prop1     20

for second element:

Column1  Column2  Column3

    A2       Prop2     15
    A3       Prop3     5 
    A4       Prop2     15

and for the fourth:

Column1  Column2  Column3

    A4       Prop4     10

hope it makes sense

fredj59
  • 1