DISTINCT and Date DESC

[brian1313]

I have this query in a ColdFusion page:

<cfquery name=&quot;list_events&quot; datasource=&quot;ksuperc&quot;>
SELECT DISTINCT
IIF (Month(event_date) BETWEEN 9 and 12,'FAL',
IIF (Month(event_date) BETWEEN 5 and 8,' SUM',
' SPR')) as event_semester,
year(event_date) as event_year
FROM events
</cfquery>

This query works fine except the output of this CFOUTPUT tag:

<table><cfoutput query=&quot;list_events&quot;><tr><td>
#event_year# #event_semester#
</td></tr></cfoutput></table>

gives me this list:

2001 SUM
2001 FAL
2002 FAL

I want to run the dates descending. My question is how since ORDER BY won't work in DISTINCT? I searched through the forum and saw a whole lot of really complex statements, but i don't think this query needs to be terribly difficult.

Thanks in advance

-b-

 

[williamu]

Hi,

You might want to try:

SELECT DISTINCT
IIF (Month(event_date) BETWEEN 9 and 12,'FAL',
IIF (Month(event_date) BETWEEN 5 and 8,' SUM',
' SPR')) as event_semester,
year(event_date) as event_year
FROM events
ORDER BY event_year DESC

HTH
--

William
Software Engineer
ICQ No. 56047340

 

[KenReay]

Hi

How about:

SELECT DISTINCT
IIF (Month(event_date) BETWEEN 9 and 12,'FAL',
IIF (Month(event_date) BETWEEN 5 and 8,' SUM',
' SPR')) as event_semester,
year(event_date) as event_year
FROM events
ORDER BY year(event_date) DESC, Month(event_date)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[brian1313]

First suggestion:
[Microsoft][ODBC Microsoft Access Driver] ORDER BY clause (event_year) conflicts with DISTINCT.

Second suggestion:
[Microsoft][ODBC Microsoft Access Driver] ORDER BY clause (Month(event_date)) conflicts with DISTINCT.

ORDER BY either does not work with DISTINCT, or I'm not using proper syntax.

Thanks in advance.

-b-

 

[brian1313]

Oops, got it now...

William and Ken were both really close...

I couldn't call 'event_year' as and ORDER BY.... and I didn't need the 'Month(event_date)'

SELECT DISTINCT
    IIF (Month(event_date) BETWEEN 9 and 12,'FAL',
    IIF (Month(event_date) BETWEEN 5 and 8,' SUM',
    ' SPR')) as event_semester,
    year(event_date) as event_year
    FROM events
    ORDER BY year(event_date) DESC

I guess all i neeed was dinner and some cake... ;-)

Thanks again you two....

-b-