Why distinct is not working here

Question

1.00/5 (4 votes)

See more:

SQL

SELECT prl.description ,
               DISTINCT
convert(varchar,prl.effective_date,103) effective_date,
    b.description brand  ,
     pc.description cat 
     FROM ProductRateList prl,Products pro,ProductCatg pc, Brands b  
     WHERE pro.Id = prl.Product_Id AND pc.Id = pro.ProdCatg_Id  AND B.Id = pro.Brand_id  AND pro.Active = 'Y' 
     order by prl.effective_date desc, 
     PRl.description ASC 
     group by convert(varchar,prl.effective_date,103),prl.description

Posted 16-Feb-12 23:37pm

Photon_

Updated 20-Feb-12 2:05am

Sarvesh Kumar Gupta

v2

Add a Solution

Comments

Om Prakash Pant 17-Feb-12 5:39am

what result you are getting?
you should get distinct records on prl.description ,convert(varchar,prl.effective_date,103) effective_date, b.description brand , pc.description cat

Photon_ 17-Feb-12 7:02am

this should be right query

SELECT DISTINCT prl.description ,
convert(varchar,prl.effective_date,103) effective_date,
b.description brand ,
pc.description cat
FROM ProductRateList prl,Products pro,ProductCatg pc, Brands b
WHERE pro.Id = prl.Product_Id AND pc.Id = pro.ProdCatg_Id AND B.Id = pro.Brand_id AND pro.Active = 'Y'

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Photon_ · Accepted Answer · 2012-02-17T01:17:00

And this could be used as exact solution which will have the order by and as well as the group by function too

SQL

SELECT DISTINCT  prl.description ,
   convert(varchar,prl.effective_date,103) effective_date,
  b.description brand  ,
   pc.description cat
   FROM ProductRateList prl,Products pro,ProductCatg pc, Brands b
   WHERE pro.Id = prl.Product_Id AND pc.Id = pro.ProdCatg_Id  AND B.Id = pro.Brand_id  AND pro.Active = 'Y'
    group by convert(varchar,prl.effective_date,103),prl.description , b.description   ,
   pc.description
   ORDER BY prl.Description

order by can contain only those column who either have aggregate function or have distinct