CODEWITHSUNDEEP
sqlsql-servert-sql
LeBlues
LeBlues

Reputation: 301

selecting latest result from a table using SQL query

I have the following table:

Data Table

From a select statement what I want is the latest unique rows (in green) for each policy. Some cause the policy information will be from the day before (all policies will not be published on the same day) .

In this scenario ACB1 has changed her last names and amounts changed.

Upvotes: 0

Views: 56

Answers (3)

wrichards0
wrichards0

Reputation: 187

Maybe if, instead of selecting all fields from the database, you did a where ID=MAX() to select the maximum ID.

So you would have:

SELECT `id`,`firstname`,`lastname`,`policy`,`amount`,`created` 
FROM `tablename` WHERE `id`=MAX()

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Use row_number window function:

select * from 
  (select *, row_number() over(partition by policy order by id desc) rn from TableName) t
where rn = 1

Upvotes: 1

user4622594
user4622594

Reputation:

This will get the latest row for each policy by id column:

    SELECT  id, policy, first, last, amount, created
    FROM    yourtable yt
            INNER JOIN
            (SELECT policy,MAX(id) as id
            FROM yourtable
GROUP BY policy) maxid ON yt.policy = maxid.policy 
                                    AND yt.id = maxid.id

Upvotes: 2

Related Questions