Raja A Raja A - 2 months ago 8
SQL Question

how to select max of row number data in a table in sql?

I have data like this,

| ID | Client | Some_Value | Row_No |
| 1 | HP | 123 | 1 |
| 1 | HP | 1245 | 2 |
| 1 | Dell | 123445 | 3 |
| 2 | HP | 111 | 1 |
| 2 | HP | 223 | 2 |
| 3 | Dell | 34 | 1 |
| 3 | Dell | 5563 | 2 |





And i need output like this ,

| ID | Client | Some_Value | Row_No |
| 1 | Dell | 123445 | 3 |
| 2 | HP | 223 | 2 |
| 3 | Dell | 5563 | 2 |





Please consider that I'm a beginner and explain me the logic.

Answer

USE Row_NUMBER() and Partition BY:

;With T AS
(
    SELECT
        ID,
        Client,
        Some_Value,
        Row_No,
        Row_NUMBER() OVER(Partition BY ID Order BY Row_No Desc) AS PartNo
    FROM TableName
)
SELECT
    ID,
    Client,
    Some_Value,
    Row_No
FROM T
WHERE T.PartNo=1

Update Statement Example:

;With T AS
(
    SELECT
        ID,
        Client,
        Some_Value,
        Row_No,
        Row_NUMBER() OVER(Partition BY ID Order BY Row_No Desc) AS PartNo
    FROM TableName
)
Update TableName
SET Name=T.Name
FROM T
WHERE T.PartNo=1 
AND TableName.Id=T.Id
Comments