Help in Query - Oracle
-
Hi Friends, I need help in the following query. SELECT A.BILL, A.BILLER, A.VERSION_NO, A.CODE, A.NAME,A.SEGMENT_CODE, A.COMMENT,B.BILL AS BILL, B.BILLER AS DW_BILLER, B.MCN AS DW_MCN, B.SO AS DW_SO, B.BG AS DW_BG, B.CODE AS DW_CODE, B.NAME AS DW_NAME,B.SEGMENT_CODE AS DW_SEGMENT_CODE FROM WEB_REF A, WEB_CUST B WHERE A.BILL=B.BILL AND A.BILLER=B.BILLER ORDER BY A.BILL The above query is working fine. and o/p like below: A3 3A 3 A3 3A 2 A3 3A 1 But now requirement is that, the table A consists VERSION_NO. For any combination that found for A.BILL=B.BILL AND A.BILLER=B.BILLER may have different VERSION_NO. Now I want to fetch only the row which is having max(version_no) for the A.BILL=B.BILL AND A.BILLER=B.BILLER the combination. I needs the o/p shd be A3 3A 3 Likewise for all the rows. Pls help me. Thanks in Advance. Regards,
-
Hi Friends, I need help in the following query. SELECT A.BILL, A.BILLER, A.VERSION_NO, A.CODE, A.NAME,A.SEGMENT_CODE, A.COMMENT,B.BILL AS BILL, B.BILLER AS DW_BILLER, B.MCN AS DW_MCN, B.SO AS DW_SO, B.BG AS DW_BG, B.CODE AS DW_CODE, B.NAME AS DW_NAME,B.SEGMENT_CODE AS DW_SEGMENT_CODE FROM WEB_REF A, WEB_CUST B WHERE A.BILL=B.BILL AND A.BILLER=B.BILLER ORDER BY A.BILL The above query is working fine. and o/p like below: A3 3A 3 A3 3A 2 A3 3A 1 But now requirement is that, the table A consists VERSION_NO. For any combination that found for A.BILL=B.BILL AND A.BILLER=B.BILLER may have different VERSION_NO. Now I want to fetch only the row which is having max(version_no) for the A.BILL=B.BILL AND A.BILLER=B.BILLER the combination. I needs the o/p shd be A3 3A 3 Likewise for all the rows. Pls help me. Thanks in Advance. Regards,
I don't know Oracle but in SQL Server I would use
Top 1andorder by version_no desc. There will be something similar in Oracle.Never underestimate the power of human stupidity RAH
-
I don't know Oracle but in SQL Server I would use
Top 1andorder by version_no desc. There will be something similar in Oracle.Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
There will be something similar in Oracle.
Yes. Oracle uses
rownum. Something like,Select * from table where rownum = 1 order by version_no descNavaneeth How to use google | Ask smart questions
-
Mycroft Holmes wrote:
There will be something similar in Oracle.
Yes. Oracle uses
rownum. Something like,Select * from table where rownum = 1 order by version_no descNavaneeth How to use google | Ask smart questions
Hi Navaneeth, Thank you so much. I understood the rownum=1 will work when i do order by version_no desc. But in this case, order by is dynamic..that is user-given input. So whatever column user wish to come as order by he ll select that. Can u help me how in that case i have to use this query. Thanks lot really!!! Regards,
-
Mycroft Holmes wrote:
There will be something similar in Oracle.
Yes. Oracle uses
rownum. Something like,Select * from table where rownum = 1 order by version_no descNavaneeth How to use google | Ask smart questions
Navaneeth, Thanks. If I user rownum=1 order by version_no desc, this will give the very first row, but i want the rows with version_no = max(version_no) in every code,unit_id combination. If I have 3 rows with this combination, among the 3 rows i wanted to display the row which is having max(version_no). Kindly help me. Thanks. Regards, Sageetha
-
Hi Friends, I need help in the following query. SELECT A.BILL, A.BILLER, A.VERSION_NO, A.CODE, A.NAME,A.SEGMENT_CODE, A.COMMENT,B.BILL AS BILL, B.BILLER AS DW_BILLER, B.MCN AS DW_MCN, B.SO AS DW_SO, B.BG AS DW_BG, B.CODE AS DW_CODE, B.NAME AS DW_NAME,B.SEGMENT_CODE AS DW_SEGMENT_CODE FROM WEB_REF A, WEB_CUST B WHERE A.BILL=B.BILL AND A.BILLER=B.BILLER ORDER BY A.BILL The above query is working fine. and o/p like below: A3 3A 3 A3 3A 2 A3 3A 1 But now requirement is that, the table A consists VERSION_NO. For any combination that found for A.BILL=B.BILL AND A.BILLER=B.BILLER may have different VERSION_NO. Now I want to fetch only the row which is having max(version_no) for the A.BILL=B.BILL AND A.BILLER=B.BILLER the combination. I needs the o/p shd be A3 3A 3 Likewise for all the rows. Pls help me. Thanks in Advance. Regards,
And just using MAX doesn't work?
SELECT A.BILL, A.BILLER, MAX(A.VERSION_NO) AS VERSION_NO,
A.CODE, A.NAME,A.SEGMENT_CODE,
A.COMMENT,B.BILL AS BILL, B.BILLER AS DW_BILLER,
B.MCN AS DW_MCN, B.SO AS DW_SO, B.BG AS DW_BG,
B.CODE AS DW_CODE, B.NAME AS DW_NAME,B.SEGMENT_CODE AS DW_SEGMENT_CODE
FROM WEB_REF A, WEB_CUST B
WHERE A.BILL=B.BILL AND A.BILLER=B.BILLER
ORDER BY A.BILLMy advice is free, and you may get what you paid for.