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
.
SELECT owner, table_name
FROM dba_tables
This is assuming that you have access to the DBA_TABLES
data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY
privilege or the SELECT_CATALOG_ROLE
role (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYS
and SYSTEM
which have large numbers of Oracle tables that you probably don't care about.
Alternatively, if you do not have access to DBA_TABLES
, you can see all the tables that your account has access to through the ALL_TABLES
view:
SELECT owner, table_name
FROM all_tables
Although, that may be a subset of the tables available in the database (ALL_TABLES
shows you the information for all the tables that your user has been granted access to).
If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES
:
SELECT table_name
FROM user_tables
Since USER_TABLES
only has information about the tables that you own, it does not have an OWNER
column – the owner, by definition, is you.
Oracle also has a number of legacy data dictionary views-- TAB
, DICT
, TABS
, and CAT
for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB
and CAT
views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES
views all filter those out. CAT
also shows information about materialized view logs with a TABLE_TYPE
of "TABLE" which is unlikely to be what you really want. DICT
combines tables and synonyms and doesn't tell you who owns the object.
Best Answer
This is how I would approach it:
Bear in mind this will also replace all multiple whitespaces, such as tabs, as well as spaces. If you really just want spaces: