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 Go to next message
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 #317393 is a reply to message #317367] Thu, 01 May 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do we know that 4 is not missing?
Define "missing".

Regards
Michel
Re: look for missing record from the same table [message #317424 is a reply to message #317393] Thu, 01 May 2008 03:17 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous message
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.
Previous Topic: SP2-0310 error
Next Topic: remove blank spaces
Goto Forum:
  


Current Time: Sat Dec 03 03:41:42 CST 2016

Total time taken to generate the page: 0.25829 seconds