Can you help me get this SQL Query

Mansoor Mohammed

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari5y ago
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 Bedhiafi5y ago
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 Singh5y ago
select BondNumber, Max(BondValue) from table group by BondNumber
Amit Gupta5y ago
It can be re-written as Select BondNumber, Max(BondValue) as BondValue from tableName group by BondNumber
Yogeshkumar Hadiya5y ago
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