Select distinct year and order by

Databases
1

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:

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.

2

Try writing a subquery then if certain functions don’t play well.


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:


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 :slight_smile:

3

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.

Haha! :slight_smile:

4

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:

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
5

wha?

wha?

nyuk nyuk nyuk

kyle nailed it

this type of query, producing a unique result set, would require no post processing in coldfusion

6

When I did this:

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:

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:

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.

7 
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
8

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. :slight_smile:

9

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 :wink:

10

I try to sneak in these compliments in hopes that they don’t go to your head. :wink: