GETTING ERROR WHEN i USE "DISTINCT" IN CFQUERY

[olchik]

Hello,

I am hiiting the wall trying to get Distinct work in my query.

Here is the code:

<cfquery name="get_admin_rank" datasource="HomePage">
SELECT rank.request_id, request.request_id, postweeks.request_id, distinct(postweeks.pw_id), request.key_postweek,
postweeks.postweek, admin_rank, headline, url, image_path, related_headline, related_url, DESCRIPTION
from postweeks
join rank
on postweeks.request_id = rank.request_id
join request
on postweeks.request_id = request.request_id
WHERE 0=0
<cfif isDefined("form.key_postweek")>
<cfif #key_postweek# is not "">
or request.key_postweek = '#form.key_postweek#'
</cfif>
</cfif>
<cfif isDefined("form.postweek")>
<cfif #form.postweek# is not "">
or postweeks.postweek = '#form.postweek#'
</cfif>
</cfif>
and REQUEST.STATUS != 'DELETED'
ORDER BY rank.admin_rank
</cfquery>

Here is the error:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'distinct'.

The error occurred in D:\Inetpub\

http://wwwroot\Secure\HomePage\admin\adminrank_results.cfm:

line 24

22 : or postweeks.postweek = '#form.postweek#'
23 : </cfif>
24 : </cfif>
25 : and REQUEST.STATUS != 'DELETED'
26 : ORDER BY rank.admin_rank


Please help!

Thank you
Olchik

 

[FALCONSEYE]

you cannot use the following

select field1, field2, field3, distinct field4
from ....

what are you trying to do?

http://www.jetansolutions.com

 

[olchik]

Hi FALCONSEYE, thank you for your response!

I will try to explaine what I am trying to do.
Initially user makes a request to post their event on Home Page of our site. They have to choose post weeks their event should appear. They can choose any amount of post weeks and one key post week. After they submit their request, admin staff should look at it and fulfill it. On the admin search page, admin staff should be able to search by post week or by key post week. The result should give them only requests for this particular week. Now the problem is, the search result displays request number of times, depends on how many weeks user selected when they submited their request. That's why I was trying to put distinct there. I only need one record for this particular week. Even if they have 5 weeks ion their record I only need to display the one admin was searching for.

Please let me know if I didn't expained it clearly.

Thank you for your help!
Olchik

 

[ecobb]

distinct(postweeks.pw_id) needs to be the first item listed in your query.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[ecobb]

...but I don't think that will give you what you want, it'll just stop the error.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[olchik]

Hi ECAR, thank you for your response!

I thought that I need distinct request_id, not pw_id. So I tried it this way... It didn't work anyway:-(

<cfquery name="get_admin_rank" datasource="HomePage">
SELECT (distinct request.request_id), <!--- rank.request_id, postweeks.request_id, --->postweeks.pw_id, request.key_postweek,
postweeks.postweek, admin_rank, headline, url, image_path, related_headline, related_url, DESCRIPTION
from request
join rank
on request.request_id = rank.request_id
join postweeks
on request.request_id = postweeks.request_id
WHERE 0=0 and REQUEST.STATUS != 'DELETED'
<cfif isDefined("form.postweek")>
<cfif #form.postweek# is not "">
and postweeks.postweek = '#form.postweek#'
</cfif>
</cfif> <cfif isDefined("form.key_postweek")>
<cfif #form.key_postweek# is not "">
and request.key_postweek = '#form.key_postweek#'
</cfif>
</cfif>

ORDER BY rank.admin_rank
</cfquery>

What do you mean by "stop the error"?

Thank you
Olchik

 

[olchik]

I GOT IT!

All I had to do was group results by request_id. It gave me what I needed.

Thanks for all your help!

Olchik