Win a copy of Head First Android this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

sql query

 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 2908
1
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is my try ,

 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2908
1
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2908
1
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have tried in oracle 9i and my query worked fine..

I am not sure about My SQL..
 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 194
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2908
1
Spring Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
reply
    Bookmark Topic Watch Topic
  • New Topic