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.

Basically, to be more specific, 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. Any guess on how I could get this? Thanks

waka
  • 2,988
  • 9
  • 31
  • 45
fredj59
  • 1
  • Out of curiosity, are you working on an existing system that you haven't designed or is this your table design in question? – N.B. Nov 17 '14 at 16:01
  • what RDBMS is this access or mysql? or access front end with mysql backend? additionally how do you define "FIRST"? order isn't guaranteed in a table. so first is subjective to an order. I don't see a timestamp, is first within each column2 alphabetical? in your example: A1 has prop1; lets suppose the first entry was 20 and the second 25... which would you pick and why? when you asked for first vs second.. if the 20/25 were reversed would that change what you wanted back for each nth element? Simply put: not enough information present to resolve: we need a date or autonumber or something. – xQbert Nov 17 '14 at 16:14
  • Do you have a unique Id? – Fionnuala Nov 17 '14 at 16:14
  • Hi, @N.B.: I am working on a project database that we designed, IDK what difference does it make? – fredj59 Nov 17 '14 at 16:34
  • Hi @xQbert: I'm a very basic user of access, I'm trying to gain some knowledge, so I'm not sure what to reply to you...This is a database in Access, I'm trying to sort create sortes tables... Yes First will be alphabethical in Column2 : Column3 does not really matter, I want the first value of column 2 + the associated values in other columns – fredj59 Nov 17 '14 at 16:36

0 Answers0