Home » SQL & PL/SQL » SQL & PL/SQL » look for missing record from the same table (oracle 10i)
| look for missing record from the same table [message #317367] |
Wed, 30 April 2008 18:35  |
rachana
Messages: 27
Registered: August 2003
|
Junior Member |
|
|
I have a table that has the following columns-
DOCID, TITLE, ATTRID, VERSION
100, Broke, 1, 1
100, Broke, 2, 1
100, Broke, 3, 1
100, Broke, 1, 2
100, Broke, 3, 2
111, Acute, 1, 1
111, Acute, 2, 3
Now, I want to find out the ATTRIDs that are missing for the LATEST(max) VERSION. (eg. attrid 2 is missing for version 2).
Eventually I want to query the entire table to find out any ATTRID that might be missing for the max VERSION of a DOCID.
Using PL/SQL procedures is not an option in my current environment. How can I accomplish this with views/sql statement?
(I have tried using NOT EXISTS and subqueries, but need some guidance)
[Updated on: Wed, 30 April 2008 18:36] Report message to a moderator |
|
|
|
|
|
| Re: look for missing record from the same table [message #317424 is a reply to message #317393] |
Thu, 01 May 2008 03:17  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
<Warning: untested SQLs>
So start off with a list of Max Versions for each DocId
SELECT docid, max(version)
FROM my_tbl
GROUP BY docid
And then we get a list of possible Attrs for each DocId
SELECT DISTINCT docid, attrid
FROM my_tbl
Now if we join these two together, we should get a MASTER LIST of possible Attrs for the max version of each Docid
SELECT a.docid, b.attrid, a.version
FROM (
SELECT docid, max(version) AS version
FROM my_tbl
GROUP BY docid
) a
JOIN (
SELECT DISTINCT docid, attrid
FROM my_tbl
) b ON a.docid = b.docid
Nearly there. We want to know which of these rows in the Master list are not present in the table:
SELECT a.docid, b.attrid, a.version
FROM (
SELECT docid, max(version) AS version
FROM my_tbl
GROUP BY docid
) a
JOIN (
SELECT DISTINCT docid, attrid
FROM my_tbl
) b ON a.docid = b.docid
WHERE (a.docid, b.attrid, a.version) NOT IN (
SELECT docid, attrid, version
FROM my_tbl
)
NOT IN queries are great if you are working with NOT NULL columns, otherwise it will be easier to use MINUS.
SELECT a.docid, b.attrid, a.version
FROM (
SELECT docid, max(version) AS version
FROM my_tbl
GROUP BY docid
) a
JOIN (
SELECT DISTINCT docid, attrid
FROM my_tbl
) b ON a.docid = b.docid
MINUS
SELECT docid, attrid, version
FROM my_tbl
Ross Leishman
|
|
|
|
| Re: look for missing record from the same table [message #317518 is a reply to message #317424] |
Thu, 01 May 2008 15:16 |
rachana
Messages: 27
Registered: August 2003
|
Junior Member |
|
|
Thanks for the sqls rleishman. I have another follow up question.
The query takes a really long time to run since the table is big. Is there a way to fine tune this sql?
Micheal, your question is good. There is no easy way to define whats missing especially since the columns reside in this one table and there are no look up tables for ATTRID or VERNUM. I can only try to give the best solution with the scenario.
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 17:01:57 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
