Select Distinct

Question

Select Distinct

Potso

SSChasing Mays

Points: 657
More actions
October 13, 2009 at 11:22 am

#136501

I am having a problem with my distinct clause. Can someone please help me how i can get this to work correctly.
select Distinct ti.trCustomerID,select ti.ItemCode,min(ti.effectdate) as addtime,
tc.company,tc.description,TT.category as Category
from dbo.trItem ti
Join TrCustomer TC on tc.trcustomerid=ti.trcustomerid
Join trcusttype TT on TT.trcusttypeid=ti.trtypeid
where tc.description not like '%Closed%'
group by ti.trcustomerid,tc.company,tc.description,category,ti.itemcode
order by ti.trcustomerid,tc.company,tc.description,category,ti.itemcode
Results of my code are below:
CompanyID ITEMID ADD COMPANY Description Category
1 1057976918:47.7 1 NRC CONTAINER
1 1057977018:47.9 1 NRC CONTAINER
1 1057977118:47.9 1 NRC CONTAINER
1 1057977218:48.1 1 NRC CONTAINER
1 1057977318:48.0 1 NRC CONTAINER
1 1061727429:16.2 1 NRC CONTAINER
I am hoping with my code that it would select the earliest Add from the group. but it seems to bring back all the data. Also i would need this to occur for each different company.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Potso,

SELECT DISTINCT is just like a GROUP BY, only it doesn't require aggregation functions. It is redundant if you are using GROUP BY, and adds nothing to your query.

You are grouping by the following columns, so the MIN() value being returned is the minimum date/time for each combination of customerID, company, description, category, and itemcode. To get the earliest time for each company, you can only group on company.

group by ti.trcustomerid,tc.company,tc.description,category,ti.itemcode

I am guessing that what you want to is to see all of the data that goes with the earliest add for each company. But, without seeing some of your source data and what the expected results should look like, I am not sure how to code a solution for you. Visual examples are much better than verbal descriptions. It would be helpful if you could supply some sample data and what you would expect correct results to look like. Please read this article [/url]to see how to set it up, so that you will get coded and tested results quicker.

By the way, the results you posted do not even match the code you posted. (I can tell by the column names.) That usually is an indication of rushing. Slow down and take your time to ask properly and you will wind up getting a solution much quicker. 🙂

Bob

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Potso SSChasing Mays Points: 657 More actions · Answer 2

It would be hard for me to show my entire datasource.

but out of the sample below i have highlighted the one result i would want to pull back

Company ITEM ID Add Date

1105797692004-03-25 15:18:47.7330001NRCCONTAINER

1105797702004-03-25 15:18:47.8900001NRCCONTAINER

1105797712004-03-25 15:18:47.9370001NRCCONTAINER

1105797722004-03-25 15:18:48.0600001NRCCONTAINER

1105797732004-03-25 15:18:47.9970001NRCCONTAINER

As you can see the data that i want is where the add date is the earliest for this particular company i dont think i can really show you how the table is set up or anything because its like 20 different fields, and there are a few tables involved.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

I'm not asking you to show all of your data, but some sample temp tables with a few rows apiece would be nice. I'm working at my regular job right now, and really don't have the time to create all the data myself, guessing at the schema. We are all unpaid volunteers here, just trying to help each other out. Please make it easy for us to help you.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

mark.bearman Valued Member Points: 57 More actions · Answer 4

with the company code different on every row then it will bring back each company code! remove that column and it works.

you could try looping around and using 'Top 1' ... probably ok if you dont have millions of rows:-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Potso, none of your sample data posts match up with each other, the column names and contents are all over the place, and your query would generate an error if you attempted to run it.

Please please set up a sample of what you need as your output, what you are currently getting, and what query you are running to get the results. The column names and types in your output should match those in your query.

Otherwise, this thread will run and run and waste everybody's time, including yours.

Your requirement is trivial, look up ROW_NUMBER() OVER(...). The problem is, everybody who has responded to this thread is scratching their heads wondering on earth you mean.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden