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
7 21651 michaelb 534
Recognized Expert Contributor
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.
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 534
Recognized Expert Contributor
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)
-
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 534
Recognized Expert Contributor
>> 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 misunderstanding here and your requirement are different. michael.
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 misunderstanding here and your requirement are different. michael.
Dear Michael,
Thank you very much... it is solved.... and i got central point.
Cheers,,,,,,
2rtist
michaelb 534
Recognized Expert Contributor Sign in to post your reply or Sign up for a free account.
Similar topics
by: Chris Styles |
last post by:
Dear All,
I've been using some code to verify form data quite happily, but i've
recently changed the way my form is structured, and I can't get it to work
now.
Originally :
The form is...
|
by: junky_fellow |
last post by:
Consider a 3-dimensional array:
char a = { "abcd", "efgh", "ijkl" ,
"mnop", "qrst", "uvwx"
} ;
when i print the addresses for a,a and a they are same.
but when i access element a+1, a+1 and...
|
by: Joseph Garvin |
last post by:
When I first came to Python I did a lot of C style loops like this:
for i in range(len(myarray)):
print myarray
Obviously the more pythonic way is:
for i in my array:
print i
|
by: Christian Christmann |
last post by:
Hi,
I'm wondering if my small example is not
"dangerous":
#define SIZE 10
char global;
char* globalPtr = global;
int main()
|
by: quantumred |
last post by:
Can I use pointer arithmetic on the members of a structure in the
following way? Should I be worried about structure padding? This works
in my debugger but I wonder if I'm bending some rule here.
...
|
by: Chuck Anderson |
last post by:
I'm pretty much a JavaScript novice. I'm good at learning by example
and changing those examples to suit my needs. That said ....
..... I have some select fields in a form I created for a...
|
by: junky_fellow |
last post by:
Hi,
I discussed about this earlier as well but I never got any
satisfactory answer. So, I am initiating this again.
Page 84, WG14/N869
"If both the pointer operand and the result point to...
|
by: Paul Brettschneider |
last post by:
Hello,
I have a global static array of structs and want to access a given
element using an identifier. I don't want to use the element subscript,
because it will change if I insert elements...
|
by: ...vagrahb |
last post by:
I am having accessing individual rows from a multidimensional array
pass to a function as reference
CODE:
function Declaration
int Part_Buffer(char (*buffer),int Low, int High)
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |