Skip to main content
S
SonOfNels
Known Participant
September 30, 2008
Question

Unique recordset

  • September 30, 2008
  • 3 replies
  • 869 views
First I must appologize as this is somewhat a repost. I don't believe I posted what my problem was well enough so I am going to try again.

I have a table that looks like this
prodid catid modlid slsid
16 sew 1 1
16 vac 1 1
16 air 1 1
17 air 1 1
17 vac 1 1
17 vac 1 1

What I want to output would be this: I want to display a unique recordset base on filtering out the duplicate records in the prodid column only.

prodid catid modlid slsid
16 sew 1 1
17 air 1 1

It does not matter which of the duplicate prodid rows the output would display. How can I do this inside of a cfquery so that the query only returns 2 rows instead of 6?
    This topic has been closed for replies.

    3 replies

    D
    Dan_Bracuk
    Inspiring
    September 30, 2008
    If you want your query to return one row for each product, you can do something like this. The syntax is for redbrick so you have to adjust it for whatever you are using.

    query
    select product_id, max(concat catid, '|', string(modlid), etc) field2
    group by product id

    output
    <cfoutput query = yourquery>
    #product_id#
    <cfloop list = "#field2#" index = "idx" delimiters = "|">
    #idx#
    closing tags
    S
    SonOfNelsAuthor
    Known Participant
    September 30, 2008
    The way that I am ouputting this data I really do not want to mess with that part of my code. That is why I am stuck on trying to figure this out using the query. Ideally I would want the query to return 2 unique records (using the table information that I originally posted, one record for prodid 16 and one record for prodid 17). Note that there will be many more unique records in my real database.
    D
    Dan_Bracuk
    Inspiring
    September 30, 2008
    run your query to select your starting recordset. Then create an array or new query object. Loop through your starting recordset and each time you get a new product id, put that record into your array/query.
    S
    SonOfNelsAuthor
    Known Participant
    September 30, 2008
    Could I use a sub select in my query to get to my goal? I am have done some reading on it but cannot make it work? Am I off base?
    A
    Anonymous
    September 30, 2008
    SELECT DISTINCT prodid, catid, modlid, slsid
    FROM TABLE
    S
    SonOfNelsAuthor
    Known Participant
    September 30, 2008
    I have tried that and it will not work because there are different catids for a single prodid. I just want the unique prodid as I described. I need to have all of the variables selected so I can ouput them.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices