CODEWITHSUNDEEP
sql
yogesh lawate
yogesh lawate

Reputation: 227

find the last record by Person

This is my Data

Id Name Amt

1  ABC  20
2  XYZ  30
3  ABC  25
4  PQR  50
5  XYZ  75
6  PQR  40

I want the last record by every particular Name like :

3  ABC  25
5  XYZ  75 
6  PQR  40

I tried group by, but i am missing some thing.

SELECT     PatientID, Balance, PReceiptNo
FROM         tblPayment
GROUP BY PatientID, Balance, PReceiptNo

Upvotes: 2

Views: 152

Answers (5)

Rory
Rory

Reputation: 41847

A possible solution:

Select p.* 
from tblPayment p
Inner join ( 
  Select name, max(id) as id
  From tblPayment 
  Group by name
) as latest on latest.id = p.id

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

One more option

SELECT *
FROM tblPayment p1
WHERE EXISTS (
              SELECT 1
              FROM tblPayment p2
              WHERE p1.Name = p2.Name
              HAVING MAX(p2.Id) = p1.Id
              )

See demo on SQLFiddle

Upvotes: 0

Egg Vans
Egg Vans

Reputation: 944

this should work

select * from (select id, name,amount from test order by id desc)as t1 group by name;

fiddle

Upvotes: 0

Patrick Kostjens
Patrick Kostjens

Reputation: 5105

Something like this should work:

SELECT p1.*
FROM tblPayment p1
LEFT JOIN tblPayment p2 ON p1.Name = p2.Name AND p1.Id < p2.Id
WHERE p2.Id IS NULL;

See this SQLFiddle

Upvotes: 6

Sergio
Sergio

Reputation: 6948

Should be similar to:

SELECT 
    id, 
    name, 
    amt
FROM
    myTable mt1
where mt1.id = (
                    SELECT 
                        MAX(id) 
                    FROM myTable mt2 
                    WHERE mt2.name = mt1.name
                )

Upvotes: 1

Related Questions