problem with my query (postgresql / php)

pokerface

Hello,

I'v a problem with one of my queries. this is how the database looks like:

tblLanguage
LID | vcvalue

tblVideo
LID | VID | vcvalue

tblDvd
LID | DID | vcvalue

...

Since there are a lot of tables, i want to make 1 query for all :-)
I need the maximum value from colomn 2 (VID, DID,...).
My query: $qry = 'select max(2) from '. $_POST["tblName"];

$_POST["tblName"] = the name of the table (tblVideo, tblDvd,...)

But with this query, I always get the value 2 (but I need the max value of the second column... not the max of "2" ;-) )
(order by 2 works, but apparently this does not) Does anyone know a solution for my problem? (Or a function that retrieves the column name or something like that)

Thanks in advance

eoghain

Sorry you are going to have to put the column name into your query. Order By 2 works because by the time that clause is executed it is already known how many columns you are retrieving and can use the index of the columns to do it's work. But when you are trying to select you need to tell the database what column you want to select from.

YappyDog

You can probably do it using two queries though.........

If you go to the system tables you can find out the name of your second column. The table I am using is called 'testing'. A query to show all columns would be:

SELECT attnum, attname
FROM pg_attribute, pg_class
WHERE attrelid = pg_class.oid
AND attnum > 0
AND not attisdropped
AND relname = 'testing'
order by attnum;

So a query to get the name of the 2nd column would be:

SELECT attname
FROM pg_attribute, pg_class
WHERE attrelid = pg_class.oid
AND attnum = 2
AND not attisdropped
AND relname = 'testing'
order by attnum;

From there you can just plug the result (column name) into your query to find the max value.