sql query

[priya pillai]

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]

This is my try ,

 SELECT sapno, plan, MAX(amt), status FROM table-name GROUP BY sapno;   

[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)

[Sagar Rohankar]

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

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

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

[Madhavi Venna]

Originally posted by Sagar Rohankar:

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

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]

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

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

and my query

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

returns almost all the rows !

What went wrong ?

[Madhavi Venna]

I have tried in oracle 9i and my query worked fine..

I am not sure about My SQL..

[Hemavathy Ramamoorthy Gopal]

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]

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]

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]

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]

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