A reader, April 14, 2003 - 5:12 pm UTC
Query maximum-1 Value
Marc, April 15, 2003 - 6:30 am UTC
Hi Tom
How can I query the "second max" value from a table ? My table has values like 1,3,5,7,9. I want see 7, because 7 is the highest value lower than max. Thanks for your answer.
Kind Regards
Marc
April 15, 2003 - 8:30 am UTC
I'll make the simplified assumption the column in question is unique.
ops$tkyte@ORA920> select * from t;
X
----------
1
3
5
7
9
ops$tkyte@ORA920>
ops$tkyte@ORA920> select x
2 from ( select x, rownum r
3 from ( select x from t order by x desc )
4 where rownum <= 2 )
5 where r = 2;
X
----------
7
ops$tkyte@ORA920>
ops$tkyte@ORA920> select x
2 from ( select x, row_number() over ( order by x desc ) r
3 from t
4 )
5 where r = 2
6 /
X
----------
7
ops$tkyte@ORA920>
ops$tkyte@ORA920> select *
2 from t
3 where (select count(*)
4 from t t2
5 where t2.x > t.x ) = 1
6 /
X
----------
7
ops$tkyte@ORA920>
ops$tkyte@ORA920> select max(x)
2 from t
3 where x < (select max(x) from t)
4 /
MAX(X)
----------
7
are a couple of ways...
A reader, April 15, 2003 - 10:13 am UTC
Good answer
Marc, April 15, 2003 - 5:28 pm UTC
Hi Tom
Cool note, but my case is a little bit more complicate. I have a table with 25 columns which I have to select the second max value for each column, group by business day (day is a date).
My table has values like:
c1 c2 c3 c...
1 8 3
2 6 9
3 4 6
4 2 0
The result should be
c1 c2 c3 c...
3 6 6
So if I use your approach, I get huge queries with 25 subqueries. Thanks in advance for your feedback.
Kind Regards
Marc
April 16, 2003 - 9:28 am UTC
ask a strange question, get a strange answer -- yes, you'll have 25 subqueries, you are asking 25 separate questions -- each column would be "sorted" separately -- 25 questions.
Views are really good for hiding this.
Second Max for each column and each date
A reader, April 15, 2003 - 9:12 pm UTC
SQL> create table t1 (c1 number, c2 number, c3 number, d date);
Table created.
SQL> insert into t1 values (1, 8, 3,trunc(sysdate));
1 row created.
SQL> insert into t1 values (2, 6, 9,trunc(sysdate));
1 row created.
SQL> insert into t1 values (3, 4, 6,trunc(sysdate));
1 row created.
SQL> insert into t1 values (4, 2, 0,trunc(sysdate));
1 row created.
SQL>
SQL> insert into t1 values (5, 9, 4,trunc(sysdate - 1));
1 row created.
SQL> insert into t1 values (6, 7, 1,trunc(sysdate - 1));
1 row created.
SQL> insert into t1 values (7, 4, 6,trunc(sysdate - 1));
1 row created.
SQL> insert into t1 values (8, 1, 2,trunc(sysdate - 1));
1 row created.
SQL>
SQL> insert into t1 values (9, 15, 6,trunc(sysdate - 2));
1 row created.
SQL> insert into t1 values (10, 9, 9,trunc(sysdate - 2));
1 row created.
SQL> insert into t1 values (11, 12, 13,trunc(sysdate - 2));
1 row created.
SQL> insert into t1 values (12, 18, 1,trunc(sysdate - 2));
1 row created.
SQL>
SQL> insert into t1 values (13, 20, 3,trunc(sysdate - 3));
1 row created.
SQL> insert into t1 values (14, 15, 9,trunc(sysdate - 3));
1 row created.
SQL>
SQL> insert into t1 values (15, 8, 1,trunc(sysdate - 4));
1 row created.
SQL>
SQL> select x.d, x.c1, y.c2, z.c3 from
2 (select c1, d from (select c1, d, row_number() over (partition by d order by c1 desc) r from t1) where r = 2) x,
3 (select c2, d from (select c2, d, row_number() over (partition by d order by c2 desc) r from t1) where r = 2) y,
4 (select c3, d from (select c3, d, row_number() over (partition by d order by c3 desc) r from t1) where r = 2) z
5 where x.d = y.d
6 and x.d = z.d;
D C1 C2 C3
--------- ---------- ---------- ----------
12-APR-03 13 15 3
13-APR-03 11 15 9
14-APR-03 7 7 4
15-APR-03 3 6 6
Displaying the maximum value for every record
K.Raghava Rao, April 16, 2003 - 12:32 am UTC
Hai tom,
Thank u tom for the answer but i have a small doubt if i add some more columns to my table eg col4,col5,col6 as per my understanding then i have to drop the view and create the view with the query so that my application will not get effect but is there any way of making the query more dynamic so that it avoids me to create the view again and again when ever i add a column to my database.
April 16, 2003 - 10:05 am UTC
look -- if you are going through the pain of adding columns to a table ( a major, huge undertaking ) recreating the corresponding view is "small time".
Use the view, everything else is dynamic sql. When dynamically discover what you need to do every time you run a query - every day -- over and over and over -- when it is trivial to make it static.
funny that you are happy to add columns (big time, huge, MASSIVE change) but cringe at the view...
don't drop the view CREATE OR REPLACE it.
Using dynamic SQL
Jianhui, April 16, 2003 - 9:57 am UTC
Displaying the maximum value for every record April 16, 2003
Reviewer: K.Raghava Rao from N.Delhi,India
Hai tom,
Thank u tom for the answer but i have a small doubt if i add some more columns
to my table eg col4,col5,col6 as per my understanding then i have to drop the
view and create the view with the query so that my application will not get
effect but is there any way of making the query more dynamic so that it avoids
me to create the view again and again when ever i add a column to my database
A quick method :
FOR rec IN (SELECT * FROM user_tab_columns
WHERE table_name='yourDynamicTable') LOOP
-- construct your query string here using the
-- columns in the data dictionary
EXECUTE IMMIDIATE yourQueryString
END LOOP;
Personally, i think it's a nasty design if you keep changing the defination of the table quite often. You should review the design instead of spending time coding to meet that not so good design. Anyway, if it happens occationally, Tom's method is pretty neat!
Many thanks to all writers
Marc, April 16, 2003 - 5:04 pm UTC
group by
arjun, August 21, 2003 - 7:01 pm UTC
hi: tom
I have my queries like :
select count(*), id from t1 group by id;
select count(*), id from t2 group by id;
select count(*), id from t3 group by id;
...
select count(*), id from tn group by id;
each query returns fixed ids (1,2,3,4,5) with varying counts :
count(*), id
10 , 1
20 , 2
30 , 3
40 , 4
50 , 5
i have to insert the results in a table mytable as
table name id1 id2 id3 id4 id5
t1 |10 | 20 | 30 | 40 | 50
how to achive this.
regards
arjun
August 21, 2003 - 7:33 pm UTC
select 't1',
count( decode(id,1,1) ) id1,
count( decode(id,2,2) ) id2,
count( decode(id,3,3) ) id3,
count( decode(id,4,4) ) id4,
count( decode(id,5,5) ) id5
from t1
UNION ALL
...
select 'tn',
count( decode(id,1,1) ) id1,
count( decode(id,2,2) ) id2,
count( decode(id,3,3) ) id3,
count( decode(id,4,4) ) id4,
count( decode(id,5,5) ) id5
from tn
Awesome !!!
A reader, August 22, 2003 - 12:50 pm UTC
hi: Tom,
Thanks for the quick response. It was simplest and the best answer.I was working on getting the solution through dynamic sql, just didn't considered the option of decode.
thanks a zillion.
regards
Awesome !!!
arjun, August 22, 2003 - 12:50 pm UTC
hi: Tom,
Thanks for the quick response. It was simplest and the best answer.I was working on getting the solution through dynamic sql, just didn't considered the option of decode.
thanks a zillion.
regards
Different situation
A reader, September 16, 2003 - 8:41 pm UTC
hello tom,
thanks always.
please help me on this :
my query returns values like
id name age
100 AA 20
100 BB 30
100 CC 40
....
I would like to dispaly as
id name1 age1 name2 age2 name3 age3
100 AA 20 BB 30 CC 40
Please help.
regards
September 16, 2003 - 8:57 pm UTC
only can do that IF there is a maximum number of rows/id
select id,
max( decode( rn, 1, name ) ) name1,
max( decode( rn, 1, age ) ) age1,
...
max( decode( rn, N, name ) ) namen,
max( decode( rn, N, age ) ) agen
from ( select id, name, age,
row_number() over (partition by id order by name ) rn
from t )
/
SELECTING THE MAXIMUM TRANSACTION DATE
Jamil, August 01, 2004 - 10:01 am UTC
Dear Tom
I want to get the maximum transaction date and I am using this script in when-validate-trigger
SELECT D_CURRENT_METER INTO LOC_VAR
FROM MAINTENANCE_HEADER A
WHERE H_TRANS_DATE =(SELECT MAX(H_TRANS_DATE) FROM MAINTENANCE_HEADER B
WHERE A.H_CAR_NO= :REC_MASTER.H_CAR_NO
AND MAINTENAMCE_STATUS = 2)
AND A.MAINTENAMCE_STATUS = 2;
Some time it is working and some time is not working any help well be appreciated .
Best regards
Jamil
August 01, 2004 - 11:14 am UTC
"Some time it is working and some time is not working any help well be
appreciated "
yeah, same with my car -- any ideas?
I would be coding:
select *
from (
select d_current_meter
from t
where x = :y
and z = 2
order by date_col desc
)
where rownum = 1;
and hoping for an index on (x,z,date_col,d_current_meter) myself (if this is run alot)
but seriously, you'd have to be a tad more specific as to what exactly "not working" means -- with an example.
SELECTING THE MAXIMUM TRANSACTION DATE
Jamil, August 02, 2004 - 3:54 am UTC
Dear Tom
Thank you very much for your help, your code it works fine in sqlplus and it gives the correct result, but when I copy the same code to my ORACLE FORMS BUILDER it gives this error message
Error 103 at line 23 column 16 encountered symbol “ORDER” when expecting one of the following) * &
When I remove the order by the error message goes but I am not getting the correct result, my code in the form as the following:
select H_TRANS_DATE,NVL(D_CURRENT_METER,0) INTO L_DATE,LOC_VAR
from (select d_current_meter,H_TRANS_DATE
FROM MAINTENANCE_HEADER
WHERE H_CAR_NO=:REC_MASTER.H_CAR_NO
AND MAINTENAMCE_STATUS = 2
order by H_TRANS_DATE desc)
where rownum = 1;
Best Regards
Jamil
August 02, 2004 - 7:54 am UTC
if your version of forms does not recognize this sql:
select *
from (
select d_current_meter
from t
where x = :y
and z = 2
order by date_col desc
)
where rownum = 1;
(it is old forms), then you can use:
select substr(max( to_char(date_col,'yyyymmddhh24miss')||d_current_meter ),15)
from t
where x = :y
and z = 2
instead.
For Jamil
Muhammad Riaz Shahid, August 02, 2004 - 5:55 am UTC
You will have to hide that one in a view and then select from view in forms.
Select the maximum value
Jamil, August 03, 2004 - 1:32 am UTC
Dear Tom
Thank you very much for your help, it works with the new code, but my form builder version as follow:
Forms [32 Bit] Version 6.0.8.11.3 (Production)
Oracle9i Enterprise Edition Release 9.2.0.1.0 – Production
It is not that much old .
Best Regards
Jamil
Getting the Maximum Value of the charcter data type
Jamil, February 23, 2005 - 7:21 am UTC
Dear Sir
I have this problem I want to get the maximum number of the character data type and my doc_code content store code from 1 to 6 character for example the doc_code as
Doc_code
110121033
110121067
110121080
110121120
My code in when-validate-item as the following
SELECT SUBSTR(A.DOC_CODE,1,6)||(MAX(TO_NUMBER(SUBSTR(A.DOC_CODE,7)))+1)
INTO :IM_TRANS_ISSUE_HEADER.DOC_CODE
FROM IM_TRANS_ISSUE_HEADER A,IM_TRANS_ISSUE_DETAILS B
WHERE A.DOC_CODE = B.DOC_CODE
AND DEL_STORE =:IM_TRANS_ISSUE_HEADER.D_S_CODE
AND A.TRANS_TYPE = :IM_TRANS_ISSUE_HEADER.TRANS_TYPE
AND ROWNUM < 2
GROUP BY
SUBSTR(A.DOC_CODE,1,6);
But I am not get the correct maximum number from doc_code field
This code return this value for the above example 110121033 as the maximum value
Waiting for your valuable answer
Best regards
Jamil Shaibani
February 23, 2005 - 9:21 am UTC
why? do you see the serious issue this has in a multi-user environment? what are you doing and why are you doing it like this?
and why do you have a number hidden in a string?
Getting the Maximum Value of the character data type and generating document code
Jamil, February 24, 2005 - 1:03 am UTC
Dear Tom
Yes my system has multi-user and multi-store and also
The document code (doc_code) is the combination of the document serial number and store code, so in this example the data store in the IM_GOODS_RECIEVE_DETAILS AS FOLLOW
doc_code Del_store
11012133 110121
11012167 110121
10201080 102010
102010100 102010
In the above doc_code the first 6 character is the Del_store code number and the serilnumber of the doc_code is sart from 7 position in the doc_code
For example if my form pass this parameters to my script
SELECT SUBSTR(A.DOC_CODE,1,6)||(MAX(TO_NUMBER(SUBSTR(A.DOC_CODE,7)))+1)
INTO :IM_TRANS_ISSUE_HEADER.DOC_CODE
FROM IM_TRANS_ISSUE_HEADER A,IM_TRANS_ISSUE_DETAILS B
WHERE A.DOC_CODE = B.DOC_CODE
AND DEL_STORE ='110121'
AND A.TRANS_TYPE = 1
AND ROWNUM < 2
GROUP BY
SUBSTR(A.DOC_CODE,1,6);
I suppose to get this result from the above script the
Maximum DOC_CODE =11012168
as the new doc_code to be assign to :IM_TRANS_ISSUE_HEADER.DOC_CODE
And if I pass parameters like this to my script
SELECT SUBSTR(A.DOC_CODE,1,6)||(MAX(TO_NUMBER(SUBSTR(A.DOC_CODE,7)))+1)
INTO :IM_TRANS_ISSUE_HEADER.DOC_CODE
FROM IM_TRANS_ISSUE_HEADER A,IM_TRANS_ISSUE_DETAILS B
WHERE A.DOC_CODE = B.DOC_CODE
AND DEL_STORE ='102010'
AND A.TRANS_TYPE = 1
AND ROWNUM < 2
GROUP BY
SUBSTR(A.DOC_CODE,1,6);
I suppose to get this result from the above script
Maximum DOC_CODE =102010101
as the new doc_code to be assign to :IM_TRANS_ISSUE_HEADER.DOC_CODE
But I am not getting the maximum doc_code ,to generate the serial number for doc_code for every issue .
Please give the answer in details or correct my script if it not correct to get the maximum doc_code and add to it 1 to genrate the new doc_code
Waiting for your valuable answer .
Best regards
Jamil
February 24, 2005 - 5:37 am UTC
do you understand what happens in a multi-user system if you attempt to generate a gap free sequence like this using MAX()?
you get duplicates. this won't work in real life with more than one user.
Getting the Maximum Value of the character data type and generating document
Jamil, February 26, 2005 - 5:55 am UTC
If I make it like this
SELECT SUBSTR(A.DOC_CODE,1,6)||Ltrim(To_char((MAX(TO_NUMBER(SUBSTR(A.doc_code,7)))+1),'fm0000'))
INTO :IM_TRANS_ISSUE_HEADER.DOC_CODE
FROM IM_TRANS_ISSUE_HEADER A,IM_TRANS_ISSUE_DETAILS B
WHERE A.DOC_CODE = B.DOC_CODE
AND DEL_STORE =:IM_TRANS_ISSUE_HEADER.D_S_CODE
GROUP BY
SUBSTR(A.DOC_CODE,1,6);
It is working, do you thank I will not face a problem in future
Best Regards
Jamil
February 26, 2005 - 7:57 am UTC
yes, I keep trying to tell you over and over "multi-user mess"
Tell you what, just open two sqlplus sessions and run this query -- if you get the same result, well, think about it........
think about what happens when two people run this at the same time in order to get a new number?
Unless you have "lock table" in your code, this is a disaster waiting to happen.
Getting the Maximum Value of the character data type and generating document
Jamil, February 27, 2005 - 7:55 am UTC
Dear Tom
Thank you very much for your cooperation, you are right when two people run this at the same time I got one header without details and revues to save the details.
Any idea how can I handle this case.
Waiting for your valuable answer.
Best regards
Jamil
February 27, 2005 - 8:36 am UTC
use sequences. that is what they were created for -- scalable, highly concurrent unique number generators.