Home » SQL & PL/SQL » SQL & PL/SQL » Aggregate function
Aggregate function [message #191477] Wed, 06 September 2006 08:56 Go to next message
kmanish420
Messages: 4
Registered: September 2006
Junior Member
Is there an aggregate function that will let read the first value of a column in a group.

For e.g I have a policy table with policyid and traveller table with travellerid, policyid, age etc
I want make a join on the 2 tables on policyid and select the age of the first traveller for that policyid.

How can I do that?
Re: Aggregate function [message #191478 is a reply to message #191477] Wed, 06 September 2006 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>select the age of the first traveller for that policyid.
In reational set theory, there is no such things as "first" record.
First based upon what metric; height, weight, IQ, distance from North Pole, etc.?
Re: Aggregate function [message #191481 is a reply to message #191478] Wed, 06 September 2006 09:03 Go to previous messageGo to next message
kmanish420
Messages: 4
Registered: September 2006
Junior Member
what i meant was based on the travellerid. so if i have policyid 1 and following 2 records for travellerid,policyid,age 1,1,50 and 2,1,42 then it should pick the age 50
Re: Aggregate function [message #191520 is a reply to message #191477] Wed, 06 September 2006 21:38 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>then it should pick the age 50
why should pick the age 50?
Based upon what invarient condition?
I'll say it again, without an ORDER BY clause, "first" indeterminate.
Re: Aggregate function [message #191525 is a reply to message #191520] Wed, 06 September 2006 22:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT policy_id
, MAX(age) KEEP (DENSE_RANK FIRST ORDER BY traveller_id)
FROM traveller
GROUP BY policy_id


Ross Leishman
Re: Aggregate function [message #191570 is a reply to message #191477] Thu, 07 September 2006 02:47 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Hi

If I understood your question properly, try this query

SELECT AGE
FROM POLICY a, TRAVELLER b
WHERE a.POLICY_ID = b.POLICY_ID
AND ROWNUM =1
ORDER BY a.ROWID


and correct me if I am wrong..

Thks & Rgds
Venkat
Re: Aggregate function [message #191599 is a reply to message #191570] Thu, 07 September 2006 04:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
you are wrong.
Order by is performed after "where rownum = 1" filter
Re: Aggregate function [message #191609 is a reply to message #191570] Thu, 07 September 2006 05:20 Go to previous messageGo to next message
kmanish420
Messages: 4
Registered: September 2006
Junior Member
Hi Venkat,

I tried your query but it works only for the first policyid.
Here are the SQL commands-

create table policy(policyid number(5));
create table traveller(travellerid number(5),policyid number(5),age number(2));
insert into policy values (1);
insert into policy values (2);
insert into traveller(travellerid,policyid,age) values(1,1,50);
insert into traveller(travellerid,policyid,age) values(2,1,42);
insert into traveller(travellerid,policyid,age) values(3,2,78);
insert into traveller(travellerid,policyid,age) values(4,2,33);
insert into traveller(travellerid,policyid,age) values(5,2,91);

Now I want the following result:-
POLICYID AGE
---------- ----------
1 50
2 78

With your query it just gives the first row of the above result.
Re: Aggregate function [message #191621 is a reply to message #191477] Thu, 07 September 2006 05:58 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Hi,

A small change @Ross query will work for u

SELECT tr.POLICYID, MAX(AGE) KEEP (DENSE_RANK FIRST ORDER BY TRAVELLERID) AGE
FROM POLICY Po, TRAVELLER tr
WHERE po.POLICYID = tr.POLICYID
GROUP BY tr.POLICYID


@Frank:

Can you plz tell me the difference here??

SQL> SELECT tr.*
2 FROM POLICY po, TRAVELLER tr
3 WHERE po.POLICYID = tr.POLICYID
4 ORDER BY tr.ROWID
5 /

TRAVELLERID POLICYID AGE
----------- ---------- ----------
1 1 50
2 1 42
3 2 78
4 2 33
5 2 91

SQL> SELECT tr.*
2 FROM POLICY po, TRAVELLER tr
3 WHERE po.POLICYID = tr.POLICYID
4 AND ROWNUM =1
5 ORDER BY tr.ROWID
6 /

TRAVELLERID POLICYID AGE
----------- ---------- ----------
1 1 50

Is there any difference with the output?? I think someone has got the same doubt yesterday. Plz clarify us if we are still wrong.

Thks & Rgds
Venkat.
Re: Aggregate function [message #191622 is a reply to message #191621] Thu, 07 September 2006 06:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Just as I said, order by will be performed AFTER the where clause.
The fact you get the same record is purely coincidental (well not 100%, but close enough)
SQL> create table policy(policyid number(5));

Table created.

SQL> create table traveller(travellerid number(5),policyid number(5),age number(2));

Table created.

SQL> insert into policy values (1);

1 row created.

SQL> insert into policy values (2);

1 row created.

SQL> insert into traveller(travellerid,policyid,age) values(1,1,50);

1 row created.

SQL> insert into traveller(travellerid,policyid,age) values(2,1,42);

1 row created.

SQL> insert into traveller(travellerid,policyid,age) values(3,2,78);

1 row created.

SQL> insert into traveller(travellerid,policyid,age) values(4,2,33);

1 row created.

SQL> insert into traveller(travellerid,policyid,age) values(5,2,91);

1 row created.

SQL> select * from traveller where rownum = 1 order by travellerid desc;

TRAVELLERID   POLICYID        AGE
----------- ---------- ----------
          1          1         50

SQL> select * from traveller where rownum = 1 order by travellerid;

TRAVELLERID   POLICYID        AGE
----------- ---------- ----------
          1          1         50
Re: Aggregate function [message #191665 is a reply to message #191622] Thu, 07 September 2006 11:14 Go to previous messageGo to next message
kmanish420
Messages: 4
Registered: September 2006
Junior Member
Thanks to Ross and Venkat
Re: Aggregate function [message #191670 is a reply to message #191665] Thu, 07 September 2006 12:07 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Since you didn't do it, I'll thank Frank for you Wink
Thank you Frank.
Previous Topic: updating multiple tables in one sql statement
Next Topic: FUNCTION-BASE INDEX for user defined function
Goto Forum:
  


Current Time: Thu Dec 08 20:20:14 CST 2016

Total time taken to generate the page: 0.13865 seconds