Skip to main content
A
Anonymous
July 1, 2011
Question

How to output 3 items per affiliate

  • July 1, 2011
  • 2 replies
  • 1941 views

Hello everybody,

We have a sort of a portal site with about 400 members. Each time a member updates his site he get's mentioned in "recent updates". The page recent updates gives 1200 results. I have got this query that get's all recent updates from all affiliates. Now I would like this query only to ouput 3 items per affiliate.

How can I do this??

Bianca

    This topic has been closed for replies.

    2 replies

    A
    Adam Cameron.
    Inspiring
    July 2, 2011

    Hi Bianca

    You can do this all on the DB side of things, and just return the data you want to CF for output.

    I wasn't too sure of your table schema or what you want outputting, but one can do this sort of thing:

    SELECT    upd.upd_timestamp, upd.aff_id, upd.upd_detail
    FROM    tbl_aff_updates AS upd
    WHERE    upd.upd_id IN (
        SELECT        TOP 3
                    upd_id
        FROM        tbl_aff_updates
        WHERE        aff_id = upd.aff_id
        ORDER BY    upd_timestamp DESC   
    )
    ORDER BY  upd.upd_timestamp DESC

    That gets up to the three most recent updates for each affiliate, and orders the whole lot in order that they happened (most recent first).

    Irrespective of your schema, the basic gist is the subquery which gets the most recent three records for a given affiliate, where the affiliate ID is the one the "main" query is currently selecting.

    Make sense?

    Note: this is SQL-Server-specific SQL.

    I could not get this working on MySQL, as it doesn't permit a LIMIT statement in a subquery as far as I can tell (going by the error message telling me as such, anyhow).  Nor could I get it working in Apache Derby, because the version that ships with CF doesn't support row limiting at all as far as I could find (this seems outlandish, so I am hopefully going to be corrected by someone there).  I did not try Oracle as my install is flaky and I can't be arsed fixing it @ the mo' (it's Saturday evening and I have wine to drink and movies to watch), but I'm sure Oracle would be able to do it fine... one would need to jump through the usual hoops one needs to to row-limit *and* order a query.

    HTH.

    --

    Adam

    A
    Anonymous
    July 3, 2011

    Hi Adam,

    Thnks again for your really kind help. I think we are close. I don't know how to apply this to the existing query as it has a JOIN statement. I tried it with a query of queries but that doesn't accept the AS statement. I should have posted the query:-) Here it is:

    <cfquery name="GetLastAddedItems" datasource="#datasource#">
        SELECT  TOP 1200       T_items_title
                            ,T_items_itemid as itemid
                            ,T_items_partnerID
                            ,T_partners_companyname as companyname
                            ,dbo.T_partners.T_partners_foldername as folder
                            ,T_partners_website as website
       
        FROM         dbo.T_items INNER JOIN
                     dbo.T_partners ON dbo.T_items.T_items_partnerID = dbo.T_partners.T_partners_id
        WHERE    (dbo.T_partners.T_partners_typepartner = 1) AND (dbo.T_items.T_items_active = 1) AND (dbo.T_items.T_items_partnerID <> 94)
        ORDER BY T_items_itemid desc
        </cfquery>

    Could you please have a look at this?

    Bianca

    A
    Adam Cameron.
    Inspiring
    July 3, 2011

    OK, the generic premise for this sort of query is as follows:

    * write a query that returns the top n sub-records for a given affiliate, as per whatever filtering / joining requirement you want,eg:

    SELECT TOP n update_id

    FROM [whatever]

    WHERE aff_id = [any of them]

    ORDER BY [whatever]

    Make sure this returns the correct records for that specific affiliate.  And it should only return the update_id (ie: the PK for the table that stores all the updates)

    * put that query aside for the time being (ie: it is not part of the next step, start from scratch)

    * next write a query which returns the columns you need to use from whatever tables you need to join to get those columns.  These are the columns you will be using in your CF code to output stuff  Also add any ordering statements you need.  But DO NOT worry about how many rows are returned for each affiliate: we'll sort this out next.

    * add a WHERE filter (or qiute possibly an AND, as you'l already have a bunch of filters) into that query:

    WHERE update_id IN (

    the first query you made

    )

    * remove the aff_id-specific filter from the first query (eg: WHERE aff_id = [any of them]), and change it to be:

    WHERE aff_id = aliasToOuterQueryUpdatesTable = aff_id

    The "secret" is you have a query that gets all the rows for all affiliates, but you then use a subquery to filter out all bar the n records for each affiliate that you want.  And the linchpin is having filtering the sub-query on the aff_id of the outer query, so it's "for each affiliate ID in the main query, filter on only these specific n records).

    The main query will be the complicated one; the subquery will be very simple: just enough to pick which three records for a given affiliate you want.

    Make sense?

    Give it a go.

    If you can't nut it out for yourself, come back @ show us where you got to and I'll have a look @ your SQL.

    I reckon pretty much all your joins will stay in the main query, you'll just need a small subquery to get the n item records you need.

    If you do need to post back, can you pls include table-create queries for each of the tables you're using.  To be hoenst - and this is probably because I am pre-coffee - I look @ your SQL and my brain just goes "waaagh", and tries to coerce me into doing something else.  ;-)

    --

    Adam

    D
    Dan_Bracuk
    Inspiring
    July 1, 2011

    Something like this

    Query of Queries selecting distinct affiliates.

    Loop through that query, and select the items for each affiliate.  Use the maxrows= 3.  Store each query in an array.

    Loop through the array and display the results.

    A
    Anonymous
    July 1, 2011

    Thanks for your reply. The DISTINCT will only select 1 item per affiliate?? I need 3 items per affiliate. I am not sure I understand well your response. I am not that good with arrays so I don't see how that will solve the question.

    Bianca

    D
    Dan_Bracuk
    Inspiring
    July 1, 2011

    Regarding " I am not sure I understand well your response"

    I'm convinced you didn't understand it at all.  I guess the phrase "selecting distinct affiliates" is open to interpretation.

    In any event, there are a couple of other methods that are potentially more efficient.  They both use a Top N philosophy, the details of which are database dependant. One is to:

    1.  Run a query that selects the distinct affiliates.

    2.  Run a union query something like this:

    select affiliate, item

    from whereever

    where 1 = 2

    <cfloop through your list of affiliates>

    union

    select affiliate and top 3 items

    where affiliate = your loop index

    </cfloop>

    The second method is to write a stored procedure that does the same thing.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices