?
Solved

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

Posted on 2006-04-09
6
Medium Priority
?
393 Views
Last Modified: 2013-12-24
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.
0
Comment
Question by:tags266
  • 4
6 Comments
 
LVL 14

Expert Comment

by:Renante Entera
ID: 16414493
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
 
LVL 14

Accepted Solution

by:
Renante Entera earned 2000 total points
ID: 16414505
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
 
LVL 2

Expert Comment

by:zomega42
ID: 16416811
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:tags266
ID: 16417172
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
 
LVL 14

Expert Comment

by:Renante Entera
ID: 16422858
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
 
LVL 14

Expert Comment

by:Renante Entera
ID: 16422904
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This installment of Make It Better gives Media Temple customers the latest news, plugins, and tutorials to make their Grid shared hosting experience that much smoother.
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month8 days, 7 hours left to enroll

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question