accessing last element of an array

[artistlikeu]

Dear all,

i have a column with data type array with variable size ...... e.g
number = [1,3,4,,7,54,98, 255]

i want to access the last element of this array.......

i accessed the first element by following statement....
select * from student where number =[1]
this worked fine....

BUT i am interested to access the last element using SQL command..... any suggestion plz.....

thnx
artist

[michaelb]

You may be able to do something like this:

 
select * from student where array_upper(number)  =  some-value;

I did not test this code, search Postgres manual for Arrays if it does not execute as expected.

[artistlikeu]

You may be able to do something like this:

 
select * from student where array_upper(number)  =  some-value;

I did not test this code, search Postgres manual for Arrays if it does not execute as expected.

This does not works.......??
may be there is a tricky query that can give the required result.??
any one can help in this regards????

thnx @rtist

[michaelb]

There's nothing tricky here aside of the fact that you'd be much better off if you don't always expect to get the complete answer, but instead be willing to take a hint, a direction so to speak, and then go and do some homework, which often involves reading a manual and spending some time trying to figure out how it works.

Let's go over this one more time. First create some table that has a string and integer array for fields.

test_db=> create table foo ( f1 varchar(20), f2 integer[] );
 CREATE TABLE

test_db=> insert into foo values ('this is a string', '{1,2,3,100,101}');
 INSERT 75356 1

Now try to use the array_upper function the way I first suggested:

test_db=> select * from foo where array_upper(f2) = 5;
 ERROR:  function array_upper(integer[]) does not exist
 HINT:  No function matches the given name and argument types. 
		You may need to add explicit type casts.

Oh, well, this is definitely not working, let's go online and read the manual at http://www.postgresql. org/docs/7.4/static/arrays.html
Just five minutes later we got some fresh ideas, let's try it again, now with correct arg list:

test_db=> select array_upper(f2, 1) from foo;
  array_upper
 -------------
			5

-- that's correct, there are indeed 5 elements in this array: {1,2,3,100,101}
-- let's try couple more things

test_db=> select * from foo where array_upper(f2, 1) = 5;
		 f1		|	   f2
 ------------------+-----------------
  this is a string | {1,2,3,100,101}
  
 (1 row)

-- this worked just fine, let's see if we can query by the value of 
-- the upper_bound array's element 

test_db=> select * from foo where f2[5] = 101;
		 f1		|	   f2
 ------------------+-----------------
  this is a string | {1,2,3,100,101}
(1 row)

-- we half way there, finally let's pretend we don't know the size of array

test_db=> select * from foo where f2[array_upper(f2,1)] = 101;
		f1		|	   f2
------------------+-----------------
  this is a string | {1,2,3,100,101}
(1 row)

[artistlikeu]

There's nothing tricky here aside of the fact that you'd be much better off if you don't always expect to get the complete answer, but instead be willing to take a hint, a direction so to speak, and then go and do some homework, which often involves reading a manual and spending some time trying to figure out how it works.

Let's go over this one more time. First create some table that has a string and integer array for fields.

test_db=> create table foo ( f1 varchar(20), f2 integer[] );
 CREATE TABLE

test_db=> insert into foo values ('this is a string', '{1,2,3,100,101}');
 INSERT 75356 1

Now try to use the array_upper function the way I first suggested:

test_db=> select * from foo where array_upper(f2) = 5;
 ERROR:  function array_upper(integer[]) does not exist
 HINT:  No function matches the given name and argument types. 
		You may need to add explicit type casts.

Oh, well, this is definitely not working, let's go online and read the manual at http://www.postgresql. org/docs/7.4/static/arrays.html
Just five minutes later we got some fresh ideas, let's try it again, now with correct arg list:

test_db=> select array_upper(f2, 1) from foo;
  array_upper
 -------------
			5

-- that's correct, there are indeed 5 elements in this array: {1,2,3,100,101}
-- let's try couple more things

test_db=> select * from foo where array_upper(f2, 1) = 5;
		 f1		|	   f2
 ------------------+-----------------
  this is a string | {1,2,3,100,101}
  
 (1 row)

-- this worked just fine, let's see if we can query by the value of 
-- the upper_bound array's element 

test_db=> select * from foo where f2[5] = 101;
		 f1		|	   f2
 ------------------+-----------------
  this is a string | {1,2,3,100,101}
(1 row)

-- we half way there, finally let's pretend we don't know the size of array

test_db=> select * from foo where f2[array_upper(f2,1)] = 101;
		f1		|	   f2
------------------+-----------------
  this is a string | {1,2,3,100,101}
(1 row)

Dear Michael,
Thank you for sparing your time. But this already have done. This is not my problem. I tell you my problem in your example.
I have to find the first element of array i.e 1. I will use following query
"select f2[1] from foo;"
this will result i row and one element
f2
-----------------------
1
1 row
Now If i have 2000 rows i can find the first element of any array without knowing the exact value of elemnt of array.
Now i am interested to find last element of array. You suppose that i do not have 1 or 2 rows. I have 69000 records for which i have to find this. Your suggestion works for few rows by mentioning element of array. For 69000 records i have to find a function or a smart query. Now if u can helop i will be grateful to you.
OR
If u tell me a query or function of postgresql that inverts the position of elements of array, it will also help me. i e if i have 30 elements in an array, function should invert elements such as 30 as 1, 29 as 2 28 as 3 and so on....

waiting kind response from any one.......
@rtist

[michaelb]

>> Now If i have 2000 rows i can find the first element of any array
>> without knowing the exact value of element of array.
>> Now i am interested to find last element of array.

Actually, the number of rows is not important here.
The code I suggested should work regardless of how many rows you have in your table.
This is the last line from my previous post, it is only slighly modified to fit your case. You may add other fields to the select list if you need them.

Select the last element of field f2 (which is a one-dimentional array of integers) from all records in table foo.

 
select f2[array_upper(f2,1)] from foo;

Let me know if we still have some misunderstandin g here and your requirement are different.

michael.

[artistlikeu]

Actually, the number of rows is not important here.
The code I suggested should work regardless of how many rows you have in your table.
This is the last line from my previous post, it is only slighly modified to fit your case. You may add other fields to the select list if you need them.

Select the last element of field f2 (which is a one-dimentional array of integers) from all records in table foo.

 
select f2[array_upper(f2,1)] from foo;

Let me know if we still have some misunderstandin g here and your requirement are different.

michael.

Dear Michael,
Thank you very much... it is solved.... and i got central point.
Cheers,,,,,,
2rtist

[michaelb]

You're welcome!