CODEWITHSUNDEEP
sqlsql-server
epaezr
epaezr

Reputation: 474

Select distinct value and bring only the latest one

I have a table that stores different statuses of each transaction. Each transaction can have multiple statuses (pending, rejected, aproved, etc).

I need to build a query that brings only the last status of each transaction.

The definition for the table that stores the statuses is:

[dbo].[Cuotas_Estado]
ID int (PK)
IdCuota int          (references table dbo.Cuotas - FK)
IdEstado int         (references table dbo.Estados - FK)

Here's the architecture for the 3 tables:

arch

When running a simple SELECT statement on table dbo.Cuotas_Estado you'll get:

SELECT
    *
FROM [dbo].[Cuotas_Estado] [E]

sel1

But the result I need is:

IdCuota    | IdEstado
2          | 1
3          | 2
9          | 3
10         | 3
11         | 4

I'm running the following select statement:

SELECT
    DISTINCT([E].[IdEstado]),
    [E].[IdCuota]
FROM [dbo].[Cuotas_Estado] [E]
ORDER BY
    [E].[IdCuota] ASC;

This will bring this result:

enter image description here

So, as you can see, it's bringing a double value to entry 9 and entry 11, I need the query to bring only the latest IdEstado column (3 in the entry 9 and 4 in the entry 11).

Upvotes: 0

Views: 52

Answers (2)

AswinRajaram
AswinRajaram

Reputation: 1622

can you try this?

with cte as (
    select IdEstado,IdCuota,
           row_number() over(partition by IdCuota order by fecha desc) as RowNum
        from [dbo].[Cuotas_Estado]
)
select IdEstado,IdCuota
    from cte
    where RowNum = 1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use a correlated subquery:

SELECT e.*
FROM [dbo].[Cuotas_Estado] e
WHERE e.IdEstado = (SELECT MAX(e2.IdEstado)
                    FROM [dbo].[Cuotas_Estado] e2
                    WHERE e2.IdCuota = e.IdCuota
                   );

With an index on Cuotas_Estado(IdCuota, IdEstado) this is probably the most efficient method.

Upvotes: 1

Related Questions