SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Select distinct year and order by

    I'm trying to pull a list of years from my databases date column for the purposes of populating a <select> list.

    I have the following query:

    Code:
    SELECT YEAR(fulldate_issued) AS year_issued
    FROM documents
    WHERE fulldate_issued IS NOT NULL
    ORDER BY year_issued DESC
    I've put this through a ColdFusion cfoutput tag and used the group= attribute to remove the duplicate years, but I'm still getting some duplicates, so I'm wondering if there's an easy way to do it in the SQL. I can't seem to use DISTINCT because it doesn't play well with ORDER BY, and I need the years to go most recent to oldest.
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,089
    Mentioned
    55 Post(s)
    Tagged
    0 Thread(s)
    Try writing a subquery then if certain functions don't play well.
    Code:
    select
    a.myYear
    from
    (
    select
    distinct year(myDate) as myYear
    from
    myTable
    ) a
    order by a.myYear desc
    Or, don't use distinct.. I dont see why this would throw any exceptions:

    Code:
    select
    year(myDate) as myYear
    from
    myTable
    Group By year(myDate)
    order by year(myDate) Desc
    Always a good day when I can beat Rudy to the punch in here

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    Or, don't use distinct.. I dont see why this would throw any exceptions:

    Code:
    select
    year(myDate) as myYear
    from
    myTable
    Group By year(myDate)
    order by year(myDate) Desc
    I tried that already, and got the dreaded "You tried to execute a query that does not include the specified expression 'YEAR(fulldate_issued)' as part of an aggregate function" error. I'll try the subquery approach.


    Quote Originally Posted by K. Wolfe View Post
    Always a good day when I can beat Rudy to the punch in here
    Haha!
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ok, weird. I got some errors trying to do a subquery, so I tried the grouping option again. Seems I needed to SELECT the exact (unaliased) column I was grouping by.

    This worked:

    Code:
    SELECT DISTINCT YEAR(fulldate_issued) AS year_issued
    	, fulldate_issued
    FROM documents
    WHERE fulldate_issued IS NOT NULL
    GROUP BY fulldate_issued 
    ORDER BY fulldate_issued DESC
    <cfset myblog = "http://cydewaze.org/">

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Seems I needed to SELECT the exact (unaliased) column I was grouping by.
    wha?

    Quote Originally Posted by cydewaze View Post
    I tried that already
    wha?

    Quote Originally Posted by K. Wolfe View Post
    Always a good day when I can beat Rudy to the punch in here
    nyuk nyuk nyuk

    kyle nailed it

    this type of query, producing a unique result set, would require no post processing in coldfusion
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    When I did this:

    Code:
    SELECT DISTINCT YEAR(fulldate_issued) AS year_issued
    FROM documents
    WHERE fulldate_issued IS NOT NULL
    GROUP BY year_issued
    ORDER BY year_issued DESC
    Or this:

    Code:
    SELECT DISTINCT YEAR(fulldate_issued) AS year_issued
    FROM documents
    WHERE fulldate_issued IS NOT NULL
    GROUP BY YEAR(fulldate_issued)
    ORDER BY year_issued DESC
    I got an error about the ORDER BY clause conflicting with SELECT DISTINCT.

    When I did this:

    Code:
    SELECT DISTINCT YEAR(fulldate_issued) AS year_issued
    , fulldate_issued
    FROM documents
    WHERE fulldate_issued IS NOT NULL
    GROUP BY year_issued
    ORDER BY fulldate_issued DESC
    It worked, except that it has to still be grouped by CF for some reason.
    <cfset myblog = "http://cydewaze.org/">

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT YEAR(fulldate_issued) AS year_issued
      FROM documents
     WHERE fulldate_issued IS NOT NULL
    GROUP 
        BY YEAR(fulldate_issued)
    ORDER 
        BY YEAR(fulldate_issued) DESC
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I've been though so many iterations of this query before posting this that I now can't remember which versions I tried, but I *think* I went wrong by trying to group and order that exact query by my year_issued alias instead of how you have it there.

    But that one (of course) works.
    <cfset myblog = "http://cydewaze.org/">

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cydewaze View Post
    But that one (of course) works.
    for the [implied] compliment, i thank you

    one of my skillz () is remembering **** from the earliest versions of sql in various sql dialects to use the absolute minimum syntax which has a chance of working

    if that makes sense
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I try to sneak in these compliments in hopes that they don't go to your head.
    <cfset myblog = "http://cydewaze.org/">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •