Alpha Video Training
Results 1 to 4 of 4

Thread: DISTINCT not being honoured in list

  1. #1
    "Certified" Alphaholic Lance Gurd's Avatar
    Real Name
    Lance Gurd
    Join Date
    Jun 2005
    Location
    Southampton, UK
    Posts
    1,444

    Default DISTINCT not being honoured in list

    I have the following MySQL query used as the select statement for my list
    Code:
    SELECT DISTINCT buildup.DESCRIPTION, buildup.TYPE, bill_pages.quote_id, buildup.UNITS, buildup.COST 
    FROM buildup buildup 
    INNER JOIN (quote_sum quote_sum INNER JOIN bill_pages bill_pages ON  quote_sum.BP_NO = bill_pages.bp_no ) 
    ON  buildup.QSUM_NO = quote_sum.QSUM_NO   
    WHERE bill_pages.quote_id = :qno  
    ORDER BY buildup.TYPE, buildup.DESCRIPTION
    in the SQL query builder it produces the desired effect of 45 records, in the list I get 256 records with a lot of repeats.

    Anybody else seen this?

    Or anybody point me to where I am going wrong?

  2. #2
    Volunteer Moderator
    Real Name
    James A. Talbott, Jr.
    Join Date
    Apr 2000
    Location
    Lexington, KY
    Posts
    1,471

    Default Re: DISTINCT not being honoured in list

    Have you tried creating a view and using the view?

    Jay
    Jay Talbott
    Lexington, KY


  3. #3
    Member
    Real Name
    Robert Earl Allin
    Join Date
    Apr 2009
    Location
    Toronto, Ontario
    Posts
    243

    Default Re: DISTINCT not being honoured in list

    What happens if you remove the order by description?

    Code:
    SELECT DISTINCT buildup.DESCRIPTION, buildup.TYPE, bill_pages.quote_id, buildup.UNITS, buildup.COST 
    FROM buildup buildup 
    INNER JOIN (quote_sum quote_sum INNER JOIN bill_pages bill_pages ON  quote_sum.BP_NO = bill_pages.bp_no ) 
    ON  buildup.QSUM_NO = quote_sum.QSUM_NO   
    WHERE bill_pages.quote_id = :qno  
    ORDER BY buildup.TYPE
    Earl Allin
    http://REAInc.net

    We can help you with mentoring and custom programming services in Alpha, .NET, PHP, ColdFusion, etc.
    PM me here for more information.

  4. #4
    "Certified" Alphaholic Lance Gurd's Avatar
    Real Name
    Lance Gurd
    Join Date
    Jun 2005
    Location
    Southampton, UK
    Posts
    1,444

    Default Re: DISTINCT not being honoured in list

    Hi Robert, taking out the order did not have any affect.

    Hi Jay, I created a view and this is where I was pointed to the problem. Originally the list had more fields which I have shrunk down to get the unique results, one of the fields was the primary field for the buildup table which was used to link to the detail view, obviously the list was still using this in some way to make the detail view work and this was invalidating the distinct statement.

    I have now taken out the detail view and gone to the old fashioned populate controls in an unbound UX action javascript and then AJAX callbacks to achieve what I needed.

    Thanks for making me look outside the box, I have a bit more work to do to get this working but can see the light at the end of the tunnel.

Similar Threads

  1. Override search using DISTINCT
    By Peter.Greulich in forum Application Server Version 11 - Web/Browser Applications
    Replies: 1
    Last Post: 09-13-2012, 06:34 PM
  2. Distinct Count
    By ehanson in forum Alpha Five Version 10 - Desktop Applications
    Replies: 5
    Last Post: 06-13-2011, 02:43 PM
  3. Select Distinct Query
    By paul.guillou in forum Alpha Five Version 7
    Replies: 3
    Last Post: 01-23-2006, 12:44 PM
  4. Distinct Count
    By George Bustos in forum Alpha Five Version 4
    Replies: 2
    Last Post: 01-15-2004, 08:41 AM
  5. Distinct
    By Ken Davis in forum Alpha Five Version 5
    Replies: 5
    Last Post: 10-25-2002, 07:49 AM

Bookmarks

Posting Permissions

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