Selecting the first row out of many sql joins

Question

Alright, so I'm putting together a path to select a revision of a particular novel:

SELECT Catalog.WbsId, Catalog.Revision, NovelRevision.Revision
  FROM Catalog, BookInCatalog
    INNER JOIN NovelMaster
      INNER JOIN HasNovelRevision
        INNER JOIN NovelRevision
        ON HasNovelRevision.right = NovelRevision.obid
      ON HasNovelRevision.Left=NovelMaster.obid
    ON NovelMaster.obid = BookInCatalog.Right
  WHERE  Catalog.obid = BookInCatalog.Left;

This returns all revisions that are in the Novel Master for each Novel Master that is in the catalog.

The problem is, I only want the FIRST revision of each novel master in the catalog. How do I go about doing that? Oh, and btw: my flavor of sql is hobbled, as many others are, in that it does not support the LIMIT Function.

*UPDATE*

So using answer 1 as a guide I upgraded my query to this:

SELECT Catalog.wbsid
FROM Catalog, BookInCatalog, NovelVersion old, NovelMaster, HasNovelRevision
LEFT JOIN       NovelVersion newRevs
ON           old.revision < newRevs.revision AND HasNovelRevision.right = newRevs.obid
LEFT JOIN       HasNovelRevision NewerHasNovelRevision
ON           NewerHasNovelRevision.right = newRevs.obid
LEFT JOIN       NovelMaster NewTecMst
ON           NewerHasNovelRevision.left = NewTecMst.obid
WHERE Catalog.programName = 'E18' AND Catalog.obid = BookInCatalog.Left
AND BookInCatalog.right = NewTecMst.obid AND newRevs.obid = null
ORDER BY newRevs.documentname;

I get an error on the fourth line: "old"."revision": invalid identifier

SOLUTION Well, I had to go to another forum, but I got a working solution:

select nr1.title, nr1.revision
from  novelrevision nr1
where nr1.revision in (select min(revision) from novelrevision nr2
    where  nr1.title = nr2.title)

So this solution uses the JOIN mentioned by the OA, along with the IN keyword to match it to a revision.

Answer 1

Something like this might work, it's called an exclusive left join:

....
INNER JOIN  NovelRevision
ON          HasNovelRevision.right = NovelRevision.obid
LEFT JOIN   NovelRevision as NewerRevision
ON          HasNovelRevision.right = NewerRevision.obid
            AND NewerRevision.revision > NovelRevision.revision
...
WHERE       NeverRevision.obid is null

The where clause filters out rows for which a newer revision exists. This effectively limits the query to the newest revisions.

In response to your comment, you could filter out only revisions that have a newer revision in the same NovelMaster. For example:

....
LEFT JOIN   NovelRevision as NewerRevision
ON          HasNovelRevision.right = NewerRevision.obid
            AND NewerRevision.revision > NovelRevision.revision
LEFT JOIN   HasNovelRevision as NewerHasNovelRevision
ON          NewerHasNovelRevision.right = NewerRevision.obid
LEFT JOIN   NovelMaster as NewerNovelMaster
ON          NewerHasNovelRevision.left = NewerNovelMaster.obid
            AND NewerNovelMaster.obid = NovelMaster.obid
....
WHERE       NeverNovelMaster.obid is null

P.S. I don't think you can group JOINs and follow them with a group of ON conditions. An ON must directly follow its JOIN.

Answer 2

You can use CTE Check this

WITH NovelRevesion_CTE(obid,RevisionDate)
AS
(
SELECT obid,MIN(RevisionDate) RevisionDate FROM NovelRevision Group by obid
)
SELECT Catalog.WbsId, Catalog.Revision, NovelRevision.Revision 
  FROM Catalog, BookInCatalog 
    INNER JOIN NovelMaster 
      INNER JOIN HasNovelRevision 
        INNER JOIN NovelRevesion
            INNER JOIN NovelRevesion_CTE
        ON HasNovelRevision.[right] = NovelRevision.obid 
      ON HasNovelRevision.[Left]=NovelMaster.obid 
    ON NovelMaster.obid = BookInCatalog.[Right]
    ON NovelRevesion_CTE.obid = NovelRevesion.obid
  WHERE  Catalog.obid = BookInCatalog.[Left];

First it select the first revision written for each novel (assuming obid is novel foriegn key) by taking the smallest date and group them. then add it as join in your query