6 Replies - 5013 Views - Last Post: 18 November 2011 - 02:33 PM Rate Topic: -----

#1 epidemik  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 14-June 08

How do you properly use distinct to get a unique query?

Posted 17 November 2011 - 08:17 PM

How do you properly use distinct to get a unique query?


 <cfquery datasource="#dsn#" name="archive">
                SELECT DISTINCT(posted)
                FROM blog
                GROUP BY posted
                </cfquery>
          		<ul class="archive">
                <cfoutput query="archive"><li><a class="selected" href="">#DATEFORMAT(posted, "mmmm yyyy")#</a> </li></cfoutput>




Is This A Good Question/Topic? 0
  • +

Replies To: How do you properly use distinct to get a unique query?

#2 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1947
  • View blog
  • Posts: 3,488
  • Joined: 13-January 08

Re: How do you properly use distinct to get a unique query?

Posted 18 November 2011 - 07:01 AM

Hey epidemik. That would depend. It would appear from your code example that you're trying to get a list of distinct posted data (dates, I assume) so you can display them as a link to something else. If so then your query is almost there.

Here's what you have now:
<cfquery datasource="#dsn#" name="archive">
   SELECT DISTINCT(posted)
   FROM blog
   GROUP BY posted
</cfquery>

<ul class="archive">
<cfoutput query="archive">
   <li><a class="selected" href="">#DATEFORMAT(posted, "mmmm yyyy")#</a> </li>
</cfoutput>


If you're looking to get distinct dates (posted) then you've got the right idea with the DISTINCT operator. Normally, you'd want to give the result of the DISTINCT selection an alias name:
SELECT DISTINCT(posted) AS posted

Some databases will do that for you while others will not. What it does is eliminates any confusion about what the column will be called on the query output so you can reference it confidently.

Now, the GROUP BY clause...I suspect that because you're posting here, your query isn't doing what it is you want and if that's the case it'll be because of this GROUP BY. The GROUP BY statement is used to...well...group the query result set by one or more columns when you've used an aggregate function. Aggregate functions are things like SUM, AVG, MAX, MIN, COUNT and so on. Basically, they're doing math or counting type functions to your data. Because you don't have an aggregate function in your query, the GROUP BY clause isn't needed...so you should eliminate it.

Do those two things and you should be good to go. If you're pulling posted and wish to display it in chronological order, you'll want to do an ORDER BY clause and then either ASC or DESC for ascending or descending order.

Good luck!
Was This Post Helpful? 0
  • +
  • -

#3 epidemik  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 14-June 08

Re: How do you properly use distinct to get a unique query?

Posted 18 November 2011 - 11:02 AM

Thank you for the help, just tried it and still got the same result, I guess i have to go deeper into it and look for another solution.

here is how my query looks now

  <cfquery datasource="#dsn#" name="archive">
                SELECT DISTINCT(posted) AS date
                FROM blog
                </cfquery>




View PostCraig328, on 18 November 2011 - 07:01 AM, said:

Hey epidemik. That would depend. It would appear from your code example that you're trying to get a list of distinct posted data (dates, I assume) so you can display them as a link to something else. If so then your query is almost there.

Here's what you have now:
<cfquery datasource="#dsn#" name="archive">
   SELECT DISTINCT(posted)
   FROM blog
   GROUP BY posted
</cfquery>

<ul class="archive">
<cfoutput query="archive">
   <li><a class="selected" href="">#DATEFORMAT(posted, "mmmm yyyy")#</a> </li>
</cfoutput>


If you're looking to get distinct dates (posted) then you've got the right idea with the DISTINCT operator. Normally, you'd want to give the result of the DISTINCT selection an alias name:
SELECT DISTINCT(posted) AS posted

Some databases will do that for you while others will not. What it does is eliminates any confusion about what the column will be called on the query output so you can reference it confidently.

Now, the GROUP BY clause...I suspect that because you're posting here, your query isn't doing what it is you want and if that's the case it'll be because of this GROUP BY. The GROUP BY statement is used to...well...group the query result set by one or more columns when you've used an aggregate function. Aggregate functions are things like SUM, AVG, MAX, MIN, COUNT and so on. Basically, they're doing math or counting type functions to your data. Because you don't have an aggregate function in your query, the GROUP BY clause isn't needed...so you should eliminate it.

Do those two things and you should be good to go. If you're pulling posted and wish to display it in chronological order, you'll want to do an ORDER BY clause and then either ASC or DESC for ascending or descending order.

Good luck!

Was This Post Helpful? 0
  • +
  • -

#4 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1947
  • View blog
  • Posts: 3,488
  • Joined: 13-January 08

Re: How do you properly use distinct to get a unique query?

Posted 18 November 2011 - 11:48 AM

What result are you getting?

BTW, try using a word other than "date" for the alias. Date is a reserved function/word for a lot of databases.
Was This Post Helpful? 0
  • +
  • -

#5 epidemik  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 14-June 08

Re: How do you properly use distinct to get a unique query?

Posted 18 November 2011 - 01:01 PM

View PostCraig328, on 18 November 2011 - 11:48 AM, said:

What result are you getting?

BTW, try using a word other than "date" for the alias. Date is a reserved function/word for a lot of databases.


This is what the output is. I just want the November 2011 to appear onces and just pull in all the post made for that month during that year

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#6 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1947
  • View blog
  • Posts: 3,488
  • Joined: 13-January 08

Re: How do you properly use distinct to get a unique query?

Posted 18 November 2011 - 01:19 PM

AH! Okay then, I immediately see what's wrong. This has less to do with your query (although to get what you're looking for you're going to want to change the query) and more to do with the DateFormat() function you're using on the output.

If you change your existing output piece from this:
<cfoutput query="archive">
   <li><a class="selected" href="">#DATEFORMAT(posted, "mmmm yyyy")#</a> </li>
</cfoutput>


...to this:
<cfoutput query="archive">
   <li><a class="selected" href="">#DATEFORMAT(posted, "mmmm, dd yyyy")#</a> </li>
</cfoutput>


...you'll see what's going on.

Because your query was asking for the distinct date, there is a difference between something like "11/04/2011" and "11/05/2011" so it pulled that. But when you were outputting the result, you were masking the days by only showing the month and year with the DateFormat function.

So, if you're looking to pull back only "Month Year" there is an easier way to do this. If you're using MySQL, the function you'll want to use is Date_Format() and with that, only pull back the unique month and year combos found in your data.

It should look something like this:
select distinct(DATE_FORMAT(blog.posted,'%M %Y')) AS MonthYear
from blog

Try running just that query somewhere and see what that gives you.
Was This Post Helpful? 1
  • +
  • -

#7 epidemik  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 14-June 08

Re: How do you properly use distinct to get a unique query?

Posted 18 November 2011 - 02:33 PM

Thank this work exactly how I needed it to work. I appreciate the help everyone

View PostCraig328, on 18 November 2011 - 01:19 PM, said:

AH! Okay then, I immediately see what's wrong. This has less to do with your query (although to get what you're looking for you're going to want to change the query) and more to do with the DateFormat() function you're using on the output.

If you change your existing output piece from this:
<cfoutput query="archive">
   <li><a class="selected" href="">#DATEFORMAT(posted, "mmmm yyyy")#</a> </li>
</cfoutput>


...to this:
<cfoutput query="archive">
   <li><a class="selected" href="">#DATEFORMAT(posted, "mmmm, dd yyyy")#</a> </li>
</cfoutput>


...you'll see what's going on.

Because your query was asking for the distinct date, there is a difference between something like "11/04/2011" and "11/05/2011" so it pulled that. But when you were outputting the result, you were masking the days by only showing the month and year with the DateFormat function.

So, if you're looking to pull back only "Month Year" there is an easier way to do this. If you're using MySQL, the function you'll want to use is Date_Format() and with that, only pull back the unique month and year combos found in your data.

It should look something like this:
select distinct(DATE_FORMAT(blog.posted,'%M %Y')) AS MonthYear
from blog

Try running just that query somewhere and see what that gives you.

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1