Get Latest Id from a Duplicate Records in a Table

Get Latest ID from a Duplicate Records in a table

select R.id,
       R.groupid,
       R.name,
       R.code
from (select id, 
             groupid, 
             name, 
             code,
             row_number() over(partition by name, code order by groupid desc) as rn
      from RawTable       
     ) as R
where R.rn = 1     

Or if you don't have row_number()

select R1.id,
       R1.groupid,
       R1.name,
       R1.code
from RawTable as R1
  inner join (  
              select name, code, max(groupid) as groupid
              from RawTable
              group by name, code
             ) as R2
    on R1.name = R2.name and
       R1.code = R2.code and
       R1.groupid = R2.groupid

Pull latest record in table with duplicate ID's

You can use LAG(), LEAD() ROW_NUMBER() function to achieve this goal. Check my below queries.

Using LAG():

SELECT 
    ID, 
    DATE, 
    col1
FROM 
(
    SELECT 
        *,
        LAG(DATE, 1) OVER(Partition By ID ORDER BY DATE DESC) AS DateOfPreviousRow
     FROM Table
) T
WHERE DateOfPreviousRow IS NULL

Using LEAD():

SELECT 
    ID, 
    DATE, 
    col1
FROM 
(
    SELECT 
        *,
        LEAD(DATE, 1) OVER(Partition By ID ORDER BY DATE) AS DateOfPreviousRow
    FROM Table
) T
WHERE DateOfPreviousRow IS NULL

Using ROW_NUMBER():

SELECT T.* FROM 
(
   SELECT 
       *,
       ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE DESC) AS ROWNumber
   FROM Table
) T
WHERE ROWNumber = 1

How to get the ID's of duplicates rows in a table

A simple method uses exists:

select t.id
from t
where exists (select 1
              from t t2
              where t2.name = t.name and
                    t2.size = t.size and
                    t2.id = 1
             );

How to get last record of duplicate data?

Use this to get the last record of each user by the latest id (incrementing primary key):

VerfiyRequest::whereIn('id', function ($query) {
    $query
        ->from('verify_requests')
        ->select(DB::raw('MAX(id) as id'))
        ->groupBy('user_id');
})->get();

Explanation: the MAX(id) as id selects latest id of each user group, so the inner query returns latest record of each user.

How to get last record of duplicate data using group by in Laravel SQL

Looking at your requirement, you can add a simple havingRaw condition where the sum of due amount is greater or equal to the paid amount till now.

DB::table('purchase_transactions')
    ->groupBy('purchases_id')
    ->havingRaw('sum(due_amount) >= sum(paid_amount)')
    ->orderBy('created_at', 'desc')
    ->get(['purchases_id', DB::raw('MAX(id) as id')]);

Note: The above query assumes that if total paid amount is greater than the total due amount, then all due is paid off and that the rows are maintained in its mathematical uniformity for each purchase_id.

If total bill amount/net payable amount is in another table then need to inner join that table with this query and little change in havingRaw() function. See the below query

DB::table('purchase_transactions')
    ->join('purchases', 'purchases.purchases_id', '=', 'purchase_transactions.purchases_id')
    ->groupBy('purchase_transactions.purchases_id')
    ->havingRaw('sum(purchases.net_payable_amount) > sum(purchase_transactions.paid_amount)')
    ->orderBy('purchase_transactions.updated_at', 'desc')
    ->get(['purchase_transactions.purchases_id as purchases_id', DB::raw('MAX(purchase_transactions.id) as id')]);

select most recent record if theres a duplicate

Try using NOT EXISTS() :

SELECT * FROM YourTable t
WHERE NOT EXISTS(SELECT 1 FROM YourTable s
                 WHERE t.date = s.date and s.created > t.created
                   AND t.name = s.name)

I think you are also missing a condition so I've added it:

and t.name = s.name

You didn't tag your RDBMS, if its SQL-Server/Oracle/Postgresql you can use ROW_NUMBER() :

SELECT s.date, s.name, s.created FROM (
    SELECT t.*,
           ROW_NUMBER() OVER(PARTITION BY t.date,t.name ORDER BY t.created DESC) as rnk
    FROM YourTable t) s
WHERE s.rnk = 1

how to pull latest record for each ID from 2 tables in Oracle SQL

Try like this below:

select p.product_id, s.product_code, p.product_name, max(s.sold_date)
from product p
left join sold_product s on s.product_id = p.product_id
group by p.product_id, s.product_code, p.product_name

select rows in sql with latest date for each ID repeated multiple times

This question has been asked before. Please see this question.

Using the accepted answer and adapting it to your problem you get:

SELECT tt.*
FROM myTable tt
INNER JOIN
    (SELECT ID, MAX(Date) AS MaxDateTime
    FROM myTable
    GROUP BY ID) groupedtt 
ON tt.ID = groupedtt.ID 
AND tt.Date = groupedtt.MaxDateTime

---

Related Topics