File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes sql query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "sql query" Watch "sql query" New topic
Author

sql query

priya pillai
Ranch Hand

Joined: Sep 12, 2007
Posts: 57
Hi

I want to write a query

In my table data like this

sapno plant amt status
a SG20 7 1
b HK10 24 4
a HK10 60 4
b SG50 20 12
a HK10 7 4
c HK10 7 4

if more than one sapno is same value then i should take record whose amt is large.that is i should display only one sapno and corresponding record whose amt is big.

the output of above should be
sapno plant amt status
a HK10 60 4
b HK10 24 4
c HK10 7 4

Regards
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

This is my try ,



[LEARNING bLOG] | [Freelance Web Designer] | [and "Rohan" is part of my surname]
Madhavi Venna
Greenhorn

Joined: Aug 06, 2008
Posts: 22
I hope this will work..


Select * from table_name
where sapno||amt in (Select sapno||max(amt) from table_name group by sapno)
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

Originally posted by Madhavi Venna:
I hope this will work..


Select * from table_name
where sapno||amt in (Select sapno||max(amt) from table_name group by sapno)


|| , Is it OR operator ? If yes, then I think we dont need it , only 'amt' ll work fine in above query , as



Pl correct me, If I`m wrong ,I`m weak in DB .
Madhavi Venna
Greenhorn

Joined: Aug 06, 2008
Posts: 22
Originally posted by Sagar Rohankar:




Pl correct me, If I`m wrong ,I`m weak in DB .


Your subquery returns 60,24,7 as amount... whatever sapno record has any of these amounts it will return but according to given requirement it should return single row for every distinct sapno.

|| is Concatenate operator here. it checks for maxium amount for every sapno and then it returns single row for verey sapno.

You can try by creating dummy table in DB with given data and check whther it works or not

[ August 21, 2008: Message edited by: Madhavi Venna ]
[ August 21, 2008: Message edited by: Madhavi Venna ]
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1



Your query results in 0 rows (I`m using MySQL 5.2)

and my query



returns almost all the rows !

What went wrong ?
Madhavi Venna
Greenhorn

Joined: Aug 06, 2008
Posts: 22
I have tried in oracle 9i and my query worked fine..

I am not sure about My SQL..
Hemavathy Ramamoorthy Gopal
Greenhorn

Joined: May 13, 2008
Posts: 17
Hi,

Please try out this query, which has been framed using MySql 5.0:

select distinct * from test1 where amt in (SELECT max(amt) from test1 group by sapno)
order by sapno,plant desc

Note : Please let me know whether it meet your requirement or not.


Regards,
Hema_RG
Kalyan Anand
Ranch Hand

Joined: Feb 07, 2007
Posts: 194
How about this

SELECT
rank() OVER (PARTITION BY sapno
ORDER BY amt DESC) AS rank_value,
plant ,
amt ,
status
FROM
my_table
WHERE
rank_value = 1
Madhavi Venna
Greenhorn

Joined: Aug 06, 2008
Posts: 22
Originally posted by Santhosh Jali:
How about this

SELECT
rank() OVER (PARTITION BY sapno
ORDER BY amt DESC) AS rank_value,
plant ,
amt ,
status
FROM
my_table
WHERE
rank_value = 1


Santhosh,

I have tried this in oracle 9i but it says rank_value is invalid identifier..Can we use Rank value column in where clause?
Sagar Rohankar
Ranch Hand

Joined: Feb 19, 2008
Posts: 2902
    
    1

Originally posted by Hemavathy Ramamoorthy Gopal:
Hi,



select distinct * from test1 where amt in (SELECT max(amt) from test1 group by sapno)
order by sapno,plant desc



For me i.e MySQL 5.2 it return all the rows groped by 'sapno' , with descending order of plant ! Not worked !
Kalyan Anand
Ranch Hand

Joined: Feb 07, 2007
Posts: 194
Yes you are correct.

Here is the corrected query :

SELECT * FROM
(
SELECT
rank() OVER (PARTITION BY sapno
ORDER BY amt DESC) AS rank_value,
plant ,
amt ,
status
FROM
my_table
)
WHERE
rank_value = 1
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: sql query