Home » SQL & PL/SQL » SQL & PL/SQL » Return First and Last values?
| Return First and Last values? [message #10062] |
Tue, 30 December 2003 06:16  |
Andrew
Messages: 144
Registered: March 1999
|
Senior Member |
|
|
Hi,
I have a query in Access that returns the First value from a field, this is an example of the query in SQL from Access: -
SELECT SUM(TABLE.LOAD_CASH) AS SUMOFLOAD, FIRST(TABLE.LOAD_CASH) AS FIRSTOFLOAD
I want to do the same thing through SQL Navigator or SQLPlusW. I've looked at the documentation in particular FIRST_VALUE and I'm a little confused ;-)
Could some one show me an example or give an explaination, as the OVER part is the bit that I got lost on?
Regards
|
|
|
|
| Re: Return First and Last values? [message #10068 is a reply to message #10062] |
Wed, 31 December 2003 00:07  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In Oracle, you have to establish an order of values before you can identify which is first. Data is not automatically returned in the order inserted or any default order. If you use just the order by clause after over, then you get the first value when rows are placed in that order. For example, using the Oracle emp demo table, if you order it, by either empno or sal, the first row contains empno 7369 and sal 800 and those would be the first values, as shown in the example below:
scott@ORA92> -- first value of empno and first value of sal
scott@ORA92> -- when ordered by empno:
scott@ORA92> select deptno, empno, sal,
2 first_value (empno) over (order by empno) as first_emp_by_empno,
3 first_value (sal) over (order by empno) as first_sal_by_empno
4 from emp
5 order by empno
6 /
DEPTNO EMPNO SAL FIRST_EMP_BY_EMPNO FIRST_SAL_BY_EMPNO
---------- ---------- ---------- ------------------ ------------------
20 7369 800 7369 800
30 7499 1600 7369 800
30 7521 1250 7369 800
20 7566 2975 7369 800
30 7654 1250 7369 800
30 7698 2850 7369 800
10 7782 2450 7369 800
20 7788 3000 7369 800
10 7839 5000 7369 800
30 7844 1500 7369 800
20 7876 1100 7369 800
30 7900 950 7369 800
20 7902 3000 7369 800
10 7934 1300 7369 800
14 rows selected.
scott@ORA92> -- first value of empno and first value of sal
scott@ORA92> -- when ordered by sal:
scott@ORA92> select deptno, empno, sal,
2 first_value (empno) over (order by sal) as first_emp_by_sal,
3 first_value (sal) over (order by sal) as first_sal_by_sal
4 from emp
5 order by sal
6 /
DEPTNO EMPNO SAL FIRST_EMP_BY_SAL FIRST_SAL_BY_SAL
---------- ---------- ---------- ---------------- ----------------
20 7369 800 7369 800
30 7900 950 7369 800
20 7876 1100 7369 800
30 7521 1250 7369 800
30 7654 1250 7369 800
10 7934 1300 7369 800
30 7844 1500 7369 800
30 7499 1600 7369 800
10 7782 2450 7369 800
30 7698 2850 7369 800
20 7566 2975 7369 800
20 7788 3000 7369 800
20 7902 3000 7369 800
10 7839 5000 7369 800
14 rows selected.
If you add the partition by clause after over, then you get the first value for each partition. For example, if you partition by deptno, then you get the first empno or first sal for each deptno. I have added a row of hyphens to separate each deptno in the examples below for easier reading. Notice that the results are different, depending on whether you order by empno or sal:
scott@ORA92> -- first value of empno and first value of sal
scott@ORA92> -- for each deptno partition
scott@ORA92> -- when ordered by empno:
scott@ORA92> select deptno, empno, sal,
2 first_value (empno) over (partition by deptno order by empno) as first_emp_by_empno,
3 first_value (sal) over (partition by deptno order by empno) as first_sal_by_empno
4 from emp
5 order by deptno, empno
6 /
DEPTNO EMPNO SAL FIRST_EMP_BY_EMPNO FIRST_SAL_BY_EMPNO
---------- ---------- ---------- ------------------ ------------------
10 7782 2450 7782 2450
10 7839 5000 7782 2450
10 7934 1300 7782 2450
----------------------------------------------------------------------
20 7369 800 7369 800
20 7566 2975 7369 800
20 7788 3000 7369 800
20 7876 1100 7369 800
20 7902 3000 7369 800
----------------------------------------------------------------------
30 7499 1600 7499 1600
30 7521 1250 7499 1600
30 7654 1250 7499 1600
30 7698 2850 7499 1600
30 7844 1500 7499 1600
30 7900 950 7499 1600
14 rows selected.
scott@ORA92> -- first value of empno and first value of sal
scott@ORA92> -- for each deptno partition
scott@ORA92> -- when ordered by sal:
scott@ORA92> select deptno, empno, sal,
2 first_value (empno) over (partition by deptno order by sal) as first_emp_by_sal,
3 first_value (sal) over (partition by deptno order by sal) as first_sal_by_sal
4 from emp
5 order by deptno, sal
6 /
DEPTNO EMPNO SAL FIRST_EMP_BY_SAL FIRST_SAL_BY_SAL
---------- ---------- ---------- ---------------- ----------------
10 7934 1300 7934 1300
10 7782 2450 7934 1300
10 7839 5000 7934 1300
----------------------------------------------------------------------
20 7369 800 7369 800
20 7876 1100 7369 800
20 7566 2975 7369 800
20 7788 3000 7369 800
20 7902 3000 7369 800
----------------------------------------------------------------------
30 7900 950 7900 950
30 7521 1250 7900 950
30 7654 1250 7900 950
30 7844 1500 7900 950
30 7499 1600 7900 950
30 7698 2850 7900 950
14 rows selected.
It may be that first_value and last_value are not what you are looking for. You may be looking for min and max or greatest and least. Those of us reading this forum don't necessarily know Access. It would help if you would provide some sample data and what results you would like, based on that sample data.
|
|
|
|
| Re: Return First and Last values? [message #10071 is a reply to message #10068] |
Wed, 31 December 2003 01:02 |
Andrew
Messages: 144
Registered: March 1999
|
Senior Member |
|
|
Barbara,
Many thanks for the reply and examples.
It is the First_Value & Last_Values I'm looking for, below is further information on what I'm trying to arrive at.
I have 4 tables: -
TB_TRANSACTIONS TR
TB_TRANS_CARDS TC
TB_CUSTOMERS CU
TB_CASHREGS CR
From which I would like to show one row of the following: -
Sum of TC.LOAD_CASH
First of TC.LOAD_CASH
Sum of TR.CARD_AMOUNT
First of TC.BAL_CASH
Last of TC.BAL_CASH
Last of TR.BOOKKEEPING_DATE
Fisrt of TC.BAL_CASH - First of TC.LOAD_CASH As Start_Bal
Joins: -
TR.ID = TC.TRANSACTION_ID
CR.ID = TR.CASHREG_ID
TR.CARD_NUM = CU.CARD_NUM
Criteria: -
WHERE TR.BOOKKEEPING_DATE BETWEEN (Date) AND (Date)
AND TR.CARD_NUM = (Card Number)
I hope this is of some help in explaining myself more clearly?
Regards,
Andrew
PS. How do I format my code in the message?
|
|
|
|
|
|
| Re: Return First and Last values? [message #10073 is a reply to message #10072] |
Wed, 31 December 2003 03:10 |
Andrew
Messages: 144
Registered: March 1999
|
Senior Member |
|
|
Barbara,
In that case it would be Order By BOOKKEEPING_DATE.
Here's some sample data: -
TB_CASHREGS
ID CODE DESCRIPTION
---------------------------
1 1 Till One
2 2 Till Two
TB_TRANSACTIONS
ID CASHREG_ID BOOKKEEPING_DATE CARD_AMOUNT CARD_NUM
------------------------------------------------------------------------
1 1 12/12/2003 0.35 004
2 2 12/12/2003 0.50 005
3 1 12/12/2003 0.75 005
4 1 13/12/2003 0.35 005
5 2 13/12/2003 0.35 005
TB_TRANS_CARDS
TRANSACTION_ID LOAD_CASH BAL_CASH
--------------------------------------------------
1 4.65
2 5.00 6.50
3 5.75
4 5.40
5 2.00 7.15
TB_CUSTOMERS
ID CODE CARD_NUM DESCRIPTION
-------------------------------------------
1 20002 005 Joe
Output with following criteria: -
TR.BOOKKEEPING_DATE BETWEEN TO_DATE('12-12-2003', 'DD.MM.YYYY') AND TO_DATE('13-12-2003', 'DD.MM.YYYY')
AND TR.CARD_NUM = '005'
SumOfLOAD_CASH FistOfLOAD_CASH SumofCARD_AMOUNT FirstOfBAL_CASH LastOfBAL_CASH LastOfBOOKKEEPING_DATE START_BAL
-------------------------------------------------------------------------------------------------------------------------
7.00 5.00 1.95 6.50 7.15 13/12/2003 1.50
Hope this helps?
Regards,
Andrew
|
|
|
|
| Re: Return First and Last values? [message #10080 is a reply to message #10073] |
Wed, 31 December 2003 20:24 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
scott@ORA92> -- test data:
scott@ORA92> SELECT * FROM tb_cashregs
2 /
ID CODE DESCRIPT
---------- ---------- --------
1 1 Till One
2 2 Till Two
scott@ORA92> SELECT * FROM tb_transactions
2 /
ID CASHREG_ID BOOKKEEPIN CARD_AMOUNT CAR
---------- ---------- ---------- ----------- ---
1 1 12/12/2003 .35 004
2 2 12/12/2003 .5 005
3 1 12/12/2003 .75 005
4 1 12/12/2003 .35 005
5 2 12/12/2003 .35 005
scott@ORA92> SELECT * FROM tb_trans_cards
2 /
TRANSACTION_ID LOAD_CASH BAL_CASH
-------------- ---------- ----------
1 4.65
2 5 6.5
3 5.75
4 5.4
5 2 7.15
scott@ORA92> SELECT * FROM tb_customers
2 /
ID CODE CAR DES
---------- ---------- --- ---
1 2002 005 Joe
scott@ORA92> -- joined and ordered data:
scott@ORA92> SELECT tc.load_cash,
2 tr.card_amount,
3 tc.bal_cash,
4 tr.bookkeeping_date,
5 tc.bal_cash - tc.load_cash
6 FROM tb_transactions tr,
7 tb_trans_cards tc,
8 tb_customers cu,
9 tb_cashregs cr
10 WHERE tr.id = tc.transaction_id
11 AND cr.id = tr.cashreg_id
12 AND tr.card_num = cu.card_num
13 AND tr.bookkeeping_date BETWEEN TO_DATE ('12-12-2003', 'DD-MM-YYYY')
14 AND TO_DATE ('13-12-2003', 'DD-MM-YYYY')
15 AND tr.card_num = '005'
16 ORDER BY tr.bookkeeping_date
17 /
LOAD_CASH CARD_AMOUNT BAL_CASH BOOKKEEPIN TC.BAL_CASH-TC.LOAD_CASH
---------- ----------- ---------- ---------- ------------------------
5 .5 6.5 12/12/2003 1.5
.75 5.75 12/12/2003
.35 5.4 12/12/2003
2 .35 7.15 12/12/2003 5.15
scott@ORA92> -- sums:
scott@ORA92> SELECT SUM (tc.load_cash) AS "SumOfLOAD_CASH",
2 SUM (tr.card_amount) AS "SumOfCARD_AMOUNT"
3 FROM tb_transactions tr,
4 tb_trans_cards tc,
5 tb_customers cu,
6 tb_cashregs cr
7 WHERE tr.id = tc.transaction_id
8 AND cr.id = tr.cashreg_id
9 AND tr.card_num = cu.card_num
10 AND tr.bookkeeping_date BETWEEN TO_DATE ('12-12-2003', 'DD-MM-YYYY')
11 AND TO_DATE ('13-12-2003', 'DD-MM-YYYY')
12 AND tr.card_num = '005'
13 /
SumOfLOAD_CASH SumOfCARD_AMOUNT
-------------- ----------------
7 1.95
scott@ORA92> -- first values, and last values:
scott@ORA92> SELECT FIRST_VALUE (tc.load_cash) OVER
2 (ORDER BY tr.bookkeeping_date) AS "FirstOfLOAD_CASH",
3 FIRST_VALUE (tc.bal_cash) OVER
4 (ORDER BY tr.bookkeeping_date) AS "FirstOfBAL_CASH",
5 LAST_VALUE (tc.bal_cash) OVER
6 (ORDER BY tr.bookkeeping_date) AS "LastOfBAL_CASH",
7 LAST_VALUE (tr.bookkeeping_date) OVER
8 (ORDER BY tr.bookkeeping_date) AS "LastOfBOOKKEEING_DATE",
9 ((FIRST_VALUE (tc.bal_cash) OVER
10 (ORDER BY tr.bookkeeping_date))
11 -(FIRST_VALUE (tc.load_cash) OVER
12 (ORDER BY tr.bookkeeping_date))) AS "START_BAL"
13 FROM tb_transactions tr,
14 tb_trans_cards tc,
15 tb_customers cu,
16 tb_cashregs cr
17 WHERE tr.id = tc.transaction_id
18 AND cr.id = tr.cashreg_id
19 AND tr.card_num = cu.card_num
20 AND tr.bookkeeping_date BETWEEN TO_DATE ('12-12-2003', 'DD-MM-YYYY')
21 AND TO_DATE ('13-12-2003', 'DD-MM-YYYY')
22 AND tr.card_num = '005'
23 AND ROWNUM = 1
24 /
FirstOfLOAD_CASH FirstOfBAL_CASH LastOfBAL_CASH LastOfBOOK START_BAL
---------------- --------------- -------------- ---------- ----------
5 6.5 6.5 12/12/2003 1.5
scott@ORA92> -- sums, first values, and last values:
scott@ORA92> SELECT sums.sum_load_cash AS "SumOfLOAD_CASH",
2 vals.first_load_cash AS "FirstOfLOAD_CASH",
3 sums.sum_card_amount AS "SumOfCARD_AMOUNT",
4 vals.first_bal_cash AS "FirstOfBAL_CASH",
5 vals.last_bal_cash AS "LastOfBAL_CASH",
6 vals.last_bookkeeping_date AS "LastOfBOOKKEEPING_DATE",
7 vals.start_bal
8 FROM (SELECT SUM (tc.load_cash) AS sum_load_cash,
9 SUM (tr.card_amount) AS sum_card_amount
10 FROM tb_transactions tr,
11 tb_trans_cards tc,
12 tb_customers cu,
13 tb_cashregs cr
14 WHERE tr.id = tc.transaction_id
15 AND cr.id = tr.cashreg_id
16 AND tr.card_num = cu.card_num
17 AND tr.bookkeeping_date BETWEEN TO_DATE ('12-12-2003', 'DD-MM-YYYY')
18 AND TO_DATE ('13-12-2003', 'DD-MM-YYYY')
19 AND tr.card_num = '005') sums,
20 (SELECT FIRST_VALUE (tc.load_cash) OVER
21 (ORDER BY tr.bookkeeping_date) AS first_load_cash,
22 FIRST_VALUE (tc.bal_cash) OVER
23 (ORDER BY tr.bookkeeping_date) AS first_bal_cash,
24 LAST_VALUE (tc.bal_cash) OVER
25 (ORDER BY tr.bookkeeping_date) AS last_bal_cash,
26 LAST_VALUE (tr.bookkeeping_date) OVER
27 (ORDER BY tr.bookkeeping_date) AS last_bookkeeping_date,
28 ((FIRST_VALUE (tc.bal_cash) OVER
29 (ORDER BY tr.bookkeeping_date))
30 -(FIRST_VALUE (tc.load_cash) OVER
31 (ORDER BY tr.bookkeeping_date))) AS start_bal
32 FROM tb_transactions tr,
33 tb_trans_cards tc,
34 tb_customers cu,
35 tb_cashregs cr
36 WHERE tr.id = tc.transaction_id
37 AND cr.id = tr.cashreg_id
38 AND tr.card_num = cu.card_num
39 AND tr.bookkeeping_date BETWEEN TO_DATE ('12-12-2003', 'DD-MM-YYYY')
40 AND TO_DATE ('13-12-2003', 'DD-MM-YYYY')
41 AND tr.card_num = '005'
42 AND ROWNUM = 1) vals
43 /
SumOfLOAD_CASH FirstOfLOAD_CASH SumOfCARD_AMOUNT FirstOfBAL_CASH LastOfBAL_CASH LastOfBOOK START_BAL
-------------- ---------------- ---------------- --------------- -------------- ---------- ----------
7 5 1.95 6.5 6.5 12/12/2003 1.5
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 17:24:23 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
