need help for a query

[asad_mian]

I need a little help with a query .

I have attached an example table (roughly) in attached picture. The table has a serial number, a supplier_id and a weight column.

We need to retrieve FOR each supplier the row that has maximum weight. There could be duplicate rows if the maximum weight for a supplier is same in two rows. I have written the expected output in the excel file as well.

Your help would really be appreciated.

[1stDAN]

select supplier_id, max(weight) from example_table group by supplier_id

[asad_mian]

Hi,
thank you very much for your reply, but the query does not give the result displayed in the expected output.

I also need the sr_no to be displayed besides. I mean I need the complete row where weight is maximum for a supplier.

[1stDAN]

I have found two solutions. First is solution you can get the maximum weight without duplicates. For supplier_id 1 you get only for sr_no 2001 and weight 80 one row.

select sr_no, supplier_id, weight 
from (select *, row_number() over (partition by supplier_id order by weight desc) as ronumer from ora_1) as tt 
where ronumer = 1 order by sr_no;

If you create new key from weight and supplier_id you can select also duplicates. So for 2001 and 2004 you get both weights 80.

select sr_no, supplier_id, weight from ora_1
where cast(weight as varchar) || '-' || cast( supplier_id as varchar) in
(select cast(max(weight) as varchar) || '-' || cast( supplier_id as varchar) from ora_1 group by supplier_id)
order by sr_no, supplier_id, weight desc;

I hope it will work for you :)

[pavan_teja]

I have found two solutions. First is solution you can get the maximum weight without duplicates. For supplier_id 1 you get only for sr_no 2001 and weight 80 one row.

select sr_no, supplier_id, weight 
from (select *, row_number() over (partition by supplier_id order by weight desc) as ronumer from ora_1) as tt 
where ronumer = 1 order by sr_no;

If you create new key from weight and supplier_id you can select also duplicates. So for 2001 and 2004 you get both weights 80.

select sr_no, supplier_id, weight from ora_1
where cast(weight as varchar) || '-' || cast( supplier_id as varchar) in
(select cast(max(weight) as varchar) || '-' || cast( supplier_id as varchar) from ora_1 group by supplier_id)
order by sr_no, supplier_id, weight desc;

I hope it will work for you :)

Yes that query posted by 1stDAN should work out..

(select sr_no,Supplier_id,weight,
row_number() over ( partition by Supplier_id order by weight desc)
row_number
from example_table order by Supplier_id)

will return the same Supplier_id's at one position followed by the weight in descending order for a particular Supplier_id.
This is followed by our row_number column appended at the end starting from 1 then 2 ,3,4.. so on for a particular Supplier_id.

The row_number depends on the the number of similar Supplier_id's.
If we have 10 similar Supplier_id's, then we will have row_number starting from 1 to 10.

For a fresh Supplier_id it will again start from 1.

Now after this the entire query i.e.

SELECT SR_NO, SUPPLIER_ID, WEIGHT FROM

(SELECT SR_NO,SUPPLIER_ID,WEIGHT,
ROW_NUMBER() OVER ( PARTITION BY SUPPLIER_ID ORDER BY WEIGHT DESC)
ROW_NUMBER
FROM EXAMPLE_TABLE ORDER BY SUPPLIER_ID)

WHERE ROW_NUMBER = 1 ORDER BY SR_NO;

will return only the ROW_NUMBER=1 so that it will return all rows only for which WEIGHT=maximum for a SUPPLIER_ID.

So good idea 1stDAN..

GOod Work:-)

[NeverLift]

I don't have an Oracle instance to test this on, but I believe the following will work:

select sr_no,Supplier_id,weight from example_table, 
(select Supplier_id sudmax, max(weight) wtmax from example_table group by Supplier_id)
where Supplier_id=sudmax and weight=wtmax
order by sr_no;

If it happens that your table content has suppliers grouped under increasing sr_no by accident and that wouldn't always be true-- which I suspect is the real situation -- and you'd like all the qualifying rows for a supplier to be adjacent and in sr_no order in each group, replace the order by clause to read

order by Supplier_id, sr_no;

Finally, if you'd like just one exmplar of all the rows for each supplier at its max weight, in supplier order, this should work:

select min(sr_no),Supplier_id,weight from example_table, 
(select Supplier_id sudmax, max(weight) wtmax from example_table group by Supplier_id)
where Supplier_id=sudmax and weight=wtmax
group by Supplier_id,weight
order by Supplier_id;

Of course, if you prefer to see the "most recent" (largest sr_no ?) row at that weight, use max instead of min, and so on.