I see many people use subqueries or else window functions to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it should work in any brand of RDBMS.
SELECT t1.*
FROM mytable t1
LEFT OUTER JOIN mytable t2
ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;
In other words: fetch the row from t1
where no other row exists with the same UserId
and a greater Date.
(I put the identifier "Date" in delimiters because it's an SQL reserved word.)
In case if t1."Date" = t2."Date"
, doubling appears. Usually tables has auto_inc(seq)
key, e.g. id
.
To avoid doubling can be used follows:
SELECT t1.*
FROM mytable t1
LEFT OUTER JOIN mytable t2
ON t1.UserId = t2.UserId AND ((t1."Date" < t2."Date")
OR (t1."Date" = t2."Date" AND t1.id < t2.id))
WHERE t2.UserId IS NULL;
Re comment from @Farhan:
Here's a more detailed explanation:
An outer join attempts to join t1
with t2
. By default, all results of t1
are returned, and if there is a match in t2
, it is also returned. If there is no match in t2
for a given row of t1
, then the query still returns the row of t1
, and uses NULL
as a placeholder for all of t2
's columns. That's just how outer joins work in general.
The trick in this query is to design the join's matching condition such that t2
must match the same userid
, and a greater date
. The idea being if a row exists in t2
that has a greater date
, then the row in t1
it's compared against can't be the greatest date
for that userid
. But if there is no match -- i.e. if no row exists in t2
with a greater date
than the row in t1
-- we know that the row in t1
was the row with the greatest date
for the given userid
.
In those cases (when there's no match), the columns of t2
will be NULL
-- even the columns specified in the join condition. So that's why we use WHERE t2.UserId IS NULL
, because we're searching for the cases where no row was found with a greater date
for the given userid
.
Syntax strictly depends on which SQL DBMS you're using. Here are some ways to do it in ANSI/ISO (aka should work on any SQL DBMS), MySQL, SQL Server, and Oracle. Be advised that my suggested ANSI/ISO method will typically be much slower than the other two methods, but if you're using a SQL DBMS other than MySQL, SQL Server, or Oracle, then it may be the only way to go (e.g. if your SQL DBMS doesn't support MERGE
):
ANSI/ISO:
update ud
set assid = (
select sale.assid
from sale
where sale.udid = ud.id
)
where exists (
select *
from sale
where sale.udid = ud.id
);
MySQL:
update ud u
inner join sale s on
u.id = s.udid
set u.assid = s.assid
SQL Server:
update u
set u.assid = s.assid
from ud u
inner join sale s on
u.id = s.udid
PostgreSQL:
update ud
set assid = s.assid
from sale s
where ud.id = s.udid;
Note that the target table must not be repeated in the FROM
clause for Postgres.
Oracle:
update
(select
u.assid as new_assid,
s.assid as old_assid
from ud u
inner join sale s on
u.id = s.udid) up
set up.new_assid = up.old_assid
SQLite:
update ud
set assid = (
select sale.assid
from sale
where sale.udid = ud.id
)
where RowID in (
select RowID
from ud
where sale.udid = ud.id
);
Best Answer
You can write a procedure with the FOR UPDATE NOWAIT and return an error message when the row is locked:
Now let's build a small example with two sessions: