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?
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.
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.
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
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.
i do not see you ever mention which db you are using, but if
it is mysql
check out the GROUP_CONCAT() function. [it concatenates data
from
different rows instead of usual concatenation of columns from
same row]
8
Replies
AdChoices