Single distinct col. On multiple selection

Question

1.00/5 (1 vote)

See more:

here i am selecting many col. but i want only

x.PurchaseOrderNumber

to be distinct.
i have used

SELECT  disctinct(x.PurchaseOrderNumber), x.DateRaised AS DateRaised, LEFT(x.Description, 100) as Description, x.TotalValue, x.SupplierName,   
  x.POStatus,  
   x.TenantRecharge, x.JobActivity, x.BuildingName, x.BuildingID, x.ReferenceNumber, x.PaymentStatus , x.ACCTNUM, x.TotalCost

but it does not serve the purpose

i have even used group by

x.PurchaseOrderNumber

but it for that i have use aggregated function which i don't want to use !!

SELECT  x.PurchaseOrderNumber, x.DateRaised AS DateRaised, LEFT(x.Description, 100) as Description, x.TotalValue, x.SupplierName,   
  x.POStatus,  
   x.TenantRecharge, x.JobActivity, x.BuildingName, x.BuildingID, x.ReferenceNumber, x.PaymentStatus , x.ACCTNUM, x.TotalCost  
  FROM  wsm_View_PODashboardList AS X
  INNER JOIN dbo.wsm_WorkOrder_PurchaseOrder wp ON wp.PurchaseOrderNumber = X.PurchaseOrderNumber
  INNER JOIN dbo.wsm_WorkOrderSchedule wo ON wo.WONumber = wp.WONumber
  where X.POStatus  in ('Created','Authorised','Sent','Matched')  
and wo.WOStatus in ('New Request','Delayed','Scheduled','Work In Progress')  
and wo.EstCompletionDate <= getdate ()  
and wo.siteid in (select wsm_ContactSite.siteid from wsm_ContactSite where wsm_ContactSite.contactid = @ContactID)  ORDER BY X.DateRaised DESC

What I have tried:

i have used

SELECT  disctinct(x.PurchaseOrderNumber), x.DateRaised AS DateRaised, LEFT(x.Description, 100) as Description, x.TotalValue, x.SupplierName,   
  x.POStatus,  
   x.TenantRecharge, x.JobActivity, x.BuildingName, x.BuildingID, x.ReferenceNumber, x.PaymentStatus , x.ACCTNUM, x.TotalCost

but it does not serve the purpose

i have even used group by

x.PurchaseOrderNumber

but it for that i have use aggregated function which i don't want to use !!

Posted 17-Mar-17 1:42am

Akhil Jain

Updated 17-Mar-17 5:51am

Add a Solution

Comments

OriginalGriff 17-Mar-17 7:50am

"it does not serve the purpose" tells us nothing - and since we have no access to your database, or any idea what output you get or expect, there isn;t a whole lot we can do.
Try showing example input and output data, and explain what part of it "does not serve the purpose"...
Use the "Improve question" widget to edit your question and provide better information.

Richard Deeming 17-Mar-17 10:09am

If you want to return distinct values for one column, then you need to tell SQL what to do when there are multiple values for the other columns.

The only way to do that is by using aggregate functions.

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)

NightWizzard · Answer 1 · 2017-03-17T05:51:00

First of all, you can't use DISTINCT on a single column. If your query returns multiple columns. DISTINCT always reduce the result to those rows, where the combination of all column values is unique.

I guess that the JOINs lead to multiple row results for each row in X because the joined tables have 1:n relations to it. If you want only a single row as result, you either have to modify the where clause so that it returns only a single match or think about what you want to return in PurchaseOrderNumber:
1) only the first match - then use TOP 1 clause to restrict the result to the first matching record
2) the MIN, MAX, AVERAGE or SUM of all PurchaseOrderNumbers in resulting rows - then use the belonging aggregate function