Can you help me get this SQL Query

Question

BondNumber BondValue 111 1000 111 2000 222 1000 222 2000 222 3000 333 1000 333 2000 333 3000 333 4000 Can u help me give a Query to get the below output BondNumber BondValue 111 2000 222 3000 333 4000

Rijwan Ansari · Answer

Try this SELECT * FROM class INNER JOIN ( SELECT BondNumber, MAX(BondValue) AS MaxBondValue FROM tableName GROUP BY BondNumber) BondValueTable ON tableName.BondNumber = BondValueTable.BondNumber AND tableName.BondValue = BondValueTable.BondValue;

Amira Bedhiafi · Answer

Since you didn't mention your RDBMS, I'm considering that you are working with SQL Server (keep in mind that you didn't mention if you can have duplicate entries for the same group): --Using GROUP BY SELECT BondNumber, MAX (BondValue) AS MaxValuePerGroup FROM Mytab GROUP BY BondNumber --Using RANK() WITH CTE AS ( SELECT *,RANK() OVER (PARTITION BY BondNumber ORDER BY BondValue DESC ) AS Rn FROM Mytab ) SELECT BondNumber,BondValue FROM CTE WHERE Rn=1 Find the DB fiddle here : *db fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4bc95bf1924af08bbfa5f4b0f578613d)*

Sachin Singh · Answer

select BondNumber, Max(BondValue) from table group by BondNumber

Amit Gupta · Answer

It can be re-written as Select BondNumber, Max(BondValue) as BondValue from tableName group by BondNumber

Yogeshkumar Hadiya · Answer

Please try below query . select distinct * from Table_1 t1 where BondValue=( select max (BondValue) from Table_1 t2 where t1.BondNumber=t2.BondNumber ) Output

BondNumber	BondValue
111	1000
111	2000
222	1000
222	2000
222	3000
333	1000
333	2000
333	3000
333	4000

Can u help me give a Query to get the below output

BondNumber	BondValue
111	2000
222	3000
333	4000

Can you help me get this SQL Query

Answers (5)