Select unique columns from a record set - no duplicated variables for a certain column

I have an output query with the following columns:

Network - Sub-Region - TimeSlot - Length - Cost

I want to be able to only output records where the network is different for each record.  I need to be able to output 5 records (maxrows="5") but only where the network is not the same as the previous record.  For example, from my query I get multiple records from the same network and since i'm only offering 5 records then i want the network to be different for each one.

Here is an example of a result
---------------------------------
1 ABC - Baltimore - 12-3pm - 30 seconds - $100
2 ABC - Baltimore - 3-6pm - 30 seconds - $125
3 CBS - Baltimore - 12-3pm - 30 seconds - $200

Here's what i want to see
-----------------------------------------
1 ABC  - Baltimore - 12-3pm - 30 seconds - $100
2 CBS - Baltimore - 12-3pm - 30 seconds - $200
etc....

It doesn't matter which record I pick, as long as the network column isn't the same.  I want to give the users a variety of networks and not the first 5 results from all the same network, just with different timeslots.

Thanks.
tags266Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Renante EnteraSenior PHP DeveloperCommented:
Hi tags266!

I think this is what you are looking for :

<cfquery name="qGetNetwork" datasource="#REQUEST.Datasource#" maxRows="5">
  SELECT DISTINCT network FROM networks
  ORDER BY network
</cfquery>

<cfset networkRow = 1>
<cfoutput query="qGetNetwork">
  <cfset currentNetwork = network>

  <cfquery name="qGetDetail" datasource="#REQUEST.Datasource#" maxRows="1">
    SELECT field1, field2, field3, field4
    FROM networks
  </cfquery>

  <cfif qGetDetail.recordcount>
    <cfloop query="qGetDetail">
      #networkRow# - #currentNetwork# - #field1# - #field2# - #field3# - #field4#<br>
    </cfloop>

    <cfset networkRow = networkRow + 1>
  </cfif>
</cfoutput>

* Note : I am just considering that (Baltimore - 12-3pm - 30 seconds - $100) are values of (field1, field2, field3, field4) respectively.

I hope that this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
Renante EnteraSenior PHP DeveloperCommented:
Oooops... Sorry, I forgot the conditional statement.

Here's the final code :
----------------------------------------------------------------------------------------------------------------
<cfquery name="qGetNetwork" datasource="#REQUEST.Datasource#" maxRows="5">
  SELECT DISTINCT network FROM networks
  ORDER BY network
</cfquery>

<cfset networkRow = 1>
<cfoutput query="qGetNetwork">
  <cfset currentNetwork = network>

  <cfquery name="qGetDetail" datasource="#REQUEST.Datasource#" maxRows="1">
    SELECT field1, field2, field3, field4
    FROM networks
    WHERE network = <cfqueryparam cfsqltype="cf_sql_varchar" value="#currentNetwork#">
  </cfquery>

  <cfif qGetDetail.recordcount>
    <cfloop query="qGetDetail">
      #networkRow# - #currentNetwork# - #field1# - #field2# - #field3# - #field4#<br>
    </cfloop>

    <cfset networkRow = networkRow + 1>
  </cfif>
</cfoutput>
----------------------------------------------------------------------------------------------------------------

Regards!
Nante :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zomega42Commented:
That will work, but if you have heavy load on your db server, or if you ever wanted to display 100 networks instead of 5, you might want to trim it down to use a single SQL statement instead of 5.  The only way I can think of to do this is by adding some extra criteria, let's say you want to show the one where "Starttime" is closest to now:

<cfquery name="getnetworks" datasource="mydsn">
select network, MIN(starttime), endtime, length cost
from mydatabasetable
group by network
</cfquery>

then do your cfoutput.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

tags266Author Commented:
HEY Nante!  Your second example worked with the networks table that i tried it on.  Below is my actual query that i have implement your code in.  Can you put it in here for me?  The query below gets me the results I need but with giving me duplicate networks.  Also would there be a way to select Distinct networks and return those results random so they're not always in aplhabetical?

<cfquery name="getUnits" datasource="people">
select *
from dbo.units, dbo.subregions, dbo.networks, dbo.timeslots
where units.subregion_id = subregions.subregion_id
and units.network_id = networks.network_id
and units.timeslot_id = timeslots.timeslot_id
and networks.network IN (#ListQualify(networks, "'")#)
</cfquery>
0
Renante EnteraSenior PHP DeveloperCommented:
Hi tags266!

First of all, I apologize for not getting back with you immediately.  Actually, it's late night(9:40PM) here in the Philippines when you reply.  Anyway, I'm glad that I have helped you.  Hope to help you again.

BTW, thanks for the points and grade.


Best regards!
eNTRANCE2002 :-)
0
Renante EnteraSenior PHP DeveloperCommented:
By the way, with regards to the implementation...

This will be the code :
<cfquery name="qGetNetwork" datasource="people" maxRows="5">
  SELECT DISTINCT network FROM networks
  WHERE network IN (#ListQualify(networks, "'")#)
</cfquery>

<cfset networkRow = 1>
<cfoutput query="qGetNetwork">
  <cfset currentNetwork = network>

  <cfquery name="qGetDetail" datasource="people" maxRows="1">
    SELECT *
    FROM dbo.units, dbo.subregions, dbo.networks, dbo.timeslots
    WHERE units.subregion_id = subregions.subregion_id
    AND units.network_id = networks.network_id
    AND units.timeslot_id = timeslots.timeslot_id
    AND networks.network = <cfqueryparam cfsqltype="cf_sql_varchar" value="#currentNetwork#">
  </cfquery>

  <cfif qGetDetail.recordcount>
    <cfloop query="qGetDetail">
      #networkRow# - #currentNetwork# - #field1# - #field2# - #field3# - #field4#<br>
    </cfloop>

    <cfset networkRow = networkRow + 1>
  </cfif>
</cfoutput>

I hope that this makes sense.  Just try it.


Cheers!
eNTRANCE2002 :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.