Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql query

 
priya pillai
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2906
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is my try ,

 
Madhavi Venna
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2906
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2906
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have tried in oracle 9i and my query worked fine..

I am not sure about My SQL..
 
Hemavathy Ramamoorthy Gopal
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Kalyan Anand
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2906
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 194
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic