How to Self-Join Table in a Way That Every Record Is Joined with The "Previous" Record

Join a table with previous record of same table

You can get a reference to the previous row using:

select h.*,
       (select h2.ActionDate
        from history h2
        where h2.LineId = h.LineId and h2.ActionDate < h.ActionDate
        order by h2.ActionDate desc
        limit 1
       ) as prev_ActionDate
from history h;

If you want the complete row, you can use a join to get the data:

select h.*, hprev.*
from (select h.*,
             (select h2.ActionDate
              from history h2
              where h2.LineId = h.LineId and h2.ActionDate < h.ActionDate
              order by h2.ActionDate desc
              limit 1
             ) as prev_ActionDate
      from history h
     ) h left join
     history hprev
     on hprev.LineId = h.LineId and hprev.ActionDate = h.prev_ActionDate;

sql self join on previous record for audit change

If you are using SQL Server 2012+, you can use lag() for a single column. In you case, you want all columns so outer apply is better:

select a.*, aprev.*
from aud a outer apply
     (select top 1 a2.*
      from aud a2
      where a2.account = a.account and a2.id < a.id
      order by id desc
     ) aprev;

This assumes that the id orders the records the same way as date_time. Your logic mixes the two -- I think it is better to stick to only one column for defining the previous record.

SQL self-join compare current record with the record of the previous date

-- Finding previous record without a LAG() function:
SELECT
    this.TransactionID, this.cardNumber , this.transactionDate
        , this.cardOldValue, this.cardNewValue 
    , prev.TransactionID, prev.transactionDate
        , prev.cardNewValue
FROM Transaction this
JOIN Transaction prev
        ON this.cardNumber = prev.cardNumber            -- same card
        AND prev.transactionDate < this.transactionDate -- older date
        AND NOT EXISTS ( SELECT *                       -- no dates in between
                FROM Transaction mid
                WHERE mid.cardNumber = this.cardNumber
                AND mid.transactionDate < this.transactionDate
                AND mid.transactionDate > prev.transactionDate
        )
WHERE this.cardOldValue > prev.cardNewValue -- suspect
        ;

Getting most recent record from table via self join

This should do:

SELECT A.*
FROM `attempts` A
INNER JOIN (SELECT USERS_ID, TESTS_ID, MAX(CREATED) MaxCreated
            FROM `attempts`
            GROUP BY USERS_ID, TESTS_ID) B
    ON A.USERS_ID = B.USERS_ID
    AND A.TESTS_ID = B.TESTS_ID
    AND A.CREATED = B.MaxCreated

Or:

SELECT A.*
FROM `attempts` A
WHERE EXISTS (SELECT 1
              FROM (SELECT USERS_ID, TESTS_ID, MAX(CREATED) MaxCreated
                    FROM `attempts`
                    GROUP BY USERS_ID, TESTS_ID) X
              WHERE X.USERS_ID = A.USERS_ID
              AND X.TESTS_ID = A.TESTS_ID
              AND X.MaxCreated = A.CREATED)

Self Join? Were Staff Who Worked the Previous Week Active 3 Weeks ago - MYSQL

If you are running MySQL 8.0, you can use window functions and a range specification:

select t.*,
    (
        max(providerid) over(
            partition by providerid 
            order by dos
            range between interval 3 week preceding and interval 3 week preceding
        ) is not null
    ) as active_3_weeks_before
from mytable t

It is not really clear from your explanation and data what you mean by was also working three weeks earlier. What the query does is, for each row, to check if another row exists with the same supplier and a dos that is exactly 3 week before the dos of the current row. This can easily be adapted for some other requirement.

---

Edit: if you want to check for any record within the last 3 weeks, you would change the window range to:

range between interval 3 week preceding and interval 1 day preceding

And if you want this in MySQL < 8.0, where window functions are not available, then you would use a correlated subquery:

select t.*,
    exists (
        select 1
        from mytable t1
        where 
            t1.providerid = t.provider_id
            and t1.dos >= t.dos - interval 3 week
            and t1.dos <  t.dos
    ) as active_3_weeks_before
from mytable t

Compare records in same table using self join

The join condition on id is the problem; this is the primary key of your table, so you end up looking for a single record that has different types and dates.

I think that this would be simpler expressed with a lateral join:

select e.*, m.id managerId, m.name mamagerName, m.yearOfJoining managerYearOfJoining
from employee e
cross apply (
    select m.*
    from employee m
    where 
        m.organisation = e.organisation 
        and m.type = 'Manager' 
        and m.yearOfJoining < e.yearOfJoining
) m
where e.type = 'Permanent'

Demo on DB Fiddle:

id | Organisation | type      | Name | yearOfJoining | mamagerName | managerYearOfJoining
-: | :----------- | :-------- | :--- | ------------: | :---------- | -------------------:
 2 | O1           | Permanent | Emp2 |          2016 | Emp1        |                 2015

This checks the date of joining of the employee agains the manager of the same organisation. On the other hand, if you want employees that joined after any manager:

select e.*
from employee e
where 
    e.type = 'Permanent'
    and not exists (
        select 1 
        from employee m
        where m.type = 'Manager' and m.yearOfJoining >= e.yearOfJoining
    )

SQL join table to itself to get data for previous year

A full join should be sufficient

  select distinct
    coalesce(a.year, b.year+1) as year
    , coalesce(a.month, b.month) as month
    , coalesce(a.product, b.product) as product
    , a.units as units
    , b.units as units_prev
  from yourtable a
  full join yourtable b on a.[year] = b.[year]+1 and a.[month] = b.[month] and a.product = b.product

Your expected results though are slightly off from the description 2018, month 2, product 2 does not exist with a prior value of 2933.

DB Fiddle : https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d01dc5bd626854b083be0864f2d5b0e4

Result :

year    month   product units   units_prev
2017    1       1       1721    
2017    2       1       4915    
2017    4       2       2933    
2017    5       1       5230    
2018    1       1               1721
2018    1       2       7672    
2018    2       1       5216    4915
2018    3       1       8911    
2018    4       2               2933
2018    5       1               5230
2019    1       2               7672
2019    2       1               5216
2019    3       1               8911

If you need to filter out futures like that, then you can add an additional where predicate, something like :

where coalesce(a.year, b.year+1) <= year(getdate())

---

Related Topics