Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2014
    Posts
    48

    Unanswered: Query To Retrieve First Record For Each Artist

    Hi Guys,

    I'm having a bit of trouble with a query. I need to retrieve the first album record for each artist in my media database. I've tried using a sub query where ArtistID from the Artist table = the ArtistID From the ArtistTitles table and that didn't work. I'm not sure what I'm missing here. Any assistance you could provide would be helpful.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I presume that by "first" you mean the first row that was entered into the table for each artist, chronologically. It's easy if your table has an Identity (Autonumber) or any kind of sequencial auto-allocated value column. Here's an example:
    Table Tbl_CF_Data:
    Code:
    CREATE TABLE [Tbl_CF_DATA]
        ( [SysCounter] COUNTER NOT NULL,
          [CLIENT] TEXT(50) NULL,
          [DESTINATAIRE] TEXT(5) NULL,
          [NUMERO] TEXT(10) NULL,
          [FUND_REQUEST] TEXT(15) NULL,
          [Archived] BIT NOT NULL
        );
    Query:
    Code:
    SELECT SysCounter, CLIENT, DESTINATAIRE, NUMERO, FUND_REQUEST
    FROM (
           (SELECT Min(Tbl_CF_DATA.SysCounter) AS MinOfSysCounter
            FROM Tbl_CF_DATA
            GROUP BY Tbl_CF_DATA.DESTINATAIRE
           ) AS a INNER JOIN
           ( SELECT Tbl_CF_DATA.SysCounter, Tbl_CF_DATA.CLIENT, Tbl_CF_DATA.DESTINATAIRE, Tbl_CF_DATA.NUMERO, Tbl_CF_DATA.FUND_REQUEST
             FROM Tbl_CF_DATA
           ) AS b ON a.MinOfSysCounter = b.SysCounter
         );
    Have a nice day!

  3. #3
    Join Date
    Sep 2014
    Posts
    48
    Thanks for your suggestion. It's a very nice and simple solution to my problem except for one small problem. One of the fields I need to return in this query is an OLE Object field containing the album cover image and you cant use Group By in this situation. I had already tried something similar to your suggestion.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so save the query, without the ole object column

    and then use that query in another query which does refer to the ole object.

    OR
    store a URL to the album art in your db as opposed to the object itself. Personally I don't store large chunks of binary data inside a DB, I will alwasy prefer storign a path /URL to the object and calling the relevant applcation to handkle that object as and when required
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you even try? The OLE object column is not part of the subquery where GROUP BY is used. It's sole purpose is to retrieve the desired primary key.
    Have a nice day!

  6. #6
    Join Date
    Sep 2014
    Posts
    48
    Not sure I understand what you're saying could you perhaps post a hypothetical example query so I could see what you're suggesting more clearly?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I did post an example. Suppose that the column FUND_REQUEST of the table Tbl_CF_DATA is an OLE object: The query will work as expected because it is composed of 2 subqueries joined by an INNER JOIN link. In these subqueries, the GROUP BY statement is only used in:
    Code:
    SELECT Min(Tbl_CF_DATA.SysCounter) AS MinOfSysCounter
            FROM Tbl_CF_DATA
            GROUP BY Tbl_CF_DATA.DESTINATAIRE
    In this (sub)query, the OLE object column FUND_REQUEST is not used. It's only used in the second (sub)query:
    Code:
    SELECT Tbl_CF_DATA.SysCounter, Tbl_CF_DATA.CLIENT, Tbl_CF_DATA.DESTINATAIRE, Tbl_CF_DATA.NUMERO, Tbl_CF_DATA.FUND_REQUEST
             FROM Tbl_CF_DATA
    where there is no GROUP BY statement.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •