By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

accessing last element of an array

P: 38
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
Dec 29 '06 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 534
You may be able to do something like this:
Expand|Select|Wrap|Line Numbers
  1.  
  2. select * from student where array_upper(number)  =  some-value; 
  3.  
I did not test this code, search Postgres manual for Arrays if it does not execute as expected.
Dec 29 '06 #2

P: 38
You may be able to do something like this:
Expand|Select|Wrap|Line Numbers
  1.  
  2. select * from student where array_upper(number)  =  some-value; 
  3.  
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
Jan 2 '07 #3

Expert 100+
P: 534
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.
Expand|Select|Wrap|Line Numbers
  1. test_db=> create table foo ( f1 varchar(20), f2 integer[] );
  2.  CREATE TABLE
  3.  
  4. test_db=> insert into foo values ('this is a string', '{1,2,3,100,101}');
  5.  INSERT 75356 1
  6.  
Now try to use the array_upper function the way I first suggested:
Expand|Select|Wrap|Line Numbers
  1. test_db=> select * from foo where array_upper(f2) = 5;
  2.  ERROR:  function array_upper(integer[]) does not exist
  3.  HINT:  No function matches the given name and argument types. 
  4.         You may need to add explicit type casts.
  5.  
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:
Expand|Select|Wrap|Line Numbers
  1. test_db=> select array_upper(f2, 1) from foo;
  2.   array_upper
  3.  -------------
  4.             5
  5.  
  6. -- that's correct, there are indeed 5 elements in this array: {1,2,3,100,101}
  7. -- let's try couple more things
  8.  
  9. test_db=> select * from foo where array_upper(f2, 1) = 5;
  10.          f1        |       f2
  11.  ------------------+-----------------
  12.   this is a string | {1,2,3,100,101}
  13.  
  14.  (1 row)
  15.  
  16. -- this worked just fine, let's see if we can query by the value of 
  17. -- the upper_bound array's element 
  18.  
  19. test_db=> select * from foo where f2[5] = 101;
  20.          f1        |       f2
  21.  ------------------+-----------------
  22.   this is a string | {1,2,3,100,101}
  23. (1 row)
  24.  
  25. -- we half way there, finally let's pretend we don't know the size of array
  26.  
  27. test_db=> select * from foo where f2[array_upper(f2,1)] = 101;
  28.         f1        |       f2
  29. ------------------+-----------------
  30.   this is a string | {1,2,3,100,101}
  31. (1 row)
  32.  
Jan 2 '07 #4

P: 38
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.
Expand|Select|Wrap|Line Numbers
  1. test_db=> create table foo ( f1 varchar(20), f2 integer[] );
  2.  CREATE TABLE
  3.  
  4. test_db=> insert into foo values ('this is a string', '{1,2,3,100,101}');
  5.  INSERT 75356 1
  6.  

Now try to use the array_upper function the way I first suggested:
Expand|Select|Wrap|Line Numbers
  1. test_db=> select * from foo where array_upper(f2) = 5;
  2.  ERROR:  function array_upper(integer[]) does not exist
  3.  HINT:  No function matches the given name and argument types. 
  4.         You may need to add explicit type casts.
  5.  
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:
Expand|Select|Wrap|Line Numbers
  1. test_db=> select array_upper(f2, 1) from foo;
  2.   array_upper
  3.  -------------
  4.             5
  5.  
  6. -- that's correct, there are indeed 5 elements in this array: {1,2,3,100,101}
  7. -- let's try couple more things
  8.  
  9. test_db=> select * from foo where array_upper(f2, 1) = 5;
  10.          f1        |       f2
  11.  ------------------+-----------------
  12.   this is a string | {1,2,3,100,101}
  13.  
  14.  (1 row)
  15.  
  16. -- this worked just fine, let's see if we can query by the value of 
  17. -- the upper_bound array's element 
  18.  
  19. test_db=> select * from foo where f2[5] = 101;
  20.          f1        |       f2
  21.  ------------------+-----------------
  22.   this is a string | {1,2,3,100,101}
  23. (1 row)
  24.  
  25. -- we half way there, finally let's pretend we don't know the size of array
  26.  
  27. test_db=> select * from foo where f2[array_upper(f2,1)] = 101;
  28.         f1        |       f2
  29. ------------------+-----------------
  30.   this is a string | {1,2,3,100,101}
  31. (1 row)
  32.  
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
Jan 3 '07 #5

Expert 100+
P: 534
>> 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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. select f2[array_upper(f2,1)] from foo;
  3.  
Let me know if we still have some misunderstanding here and your requirement are different.

michael.
Jan 3 '07 #6

P: 38
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. select f2[array_upper(f2,1)] from foo;
  3.  
Let me know if we still have some misunderstanding here and your requirement are different.

michael.
Dear Michael,
Thank you very much... it is solved.... and i got central point.
Cheers,,,,,,
2rtist
Jan 3 '07 #7

Expert 100+
P: 534
You're welcome!
Jan 4 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.