Distinct Keyword

Question

Distinct Keyword

todd.ayers

SSCommitted

Points: 1837
More actions
July 10, 2013 at 6:40 am

#296226

I am wanting to add a distinct keyword to this statement and I am not sure how or where exactly to add it.
SELECT Location.[Stack ID], Location.Location, Location.[Move Date], Location.[Move Time]
FROM Location
WHERE (((Location.[Move Date])=[Please enter the date]));
I am needing to eliminate duplicates in the [Stack ID] fields within the Location table.

Viewing 11 posts - 1 through 10 (of 10 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If you want to remove the duplicates only from StackID, which values of Location, Move Date and Move Time will you choose for each value of StackID?

John

todd.ayers SSCommitted Points: 1837 More actions · Answer 2

The query currently pulls information from the selected fields and I want to be able to keep doing that but also eliminate duplicate info.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Let me rephrase my question. Please will you post a sample result set from your original query, and then show us how you want it to look instead.

John

todd.ayers SSCommitted Points: 1837 More actions · Answer 4

Let me ask this:

In order to use the distinct keyword, would I need to select every field I am getting information from?

I am writing SQL in a query in ACCESS and am not sure really how to post my current result that I am trying to modify.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Yes, you would. DISTINCT returns a unique set of rows across the whole column list. You could use GROUP BY if you wanted uniqueness across a subset of the column list, but you'd have to have aggregate functions on the remaining columns, which is why I asked what you want the final result set to look like.

John

todd.ayers SSCommitted Points: 1837 More actions · Answer 6

Well.... I have four fields called Stack ID, Location, Move Date, and Move Time.

The Stack ID refers to a bundle of wood and in that column we have multiple stack id's on any given day. We move them from one location to another but if we move a bundle from one location to another in the same day, that bundle shows up twice in the query.... in two different locations. I am trying to get the query to still pull the entire list of stack id's, locations, move date etc. but only show the stack ids only once.

For instance stack id: 299794 shows in location: Plant and location: Dry Storage because it was moved twice on 7/8.

I pray this makes sense?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

I'm afraid it doesn't. It's much easier to understand actual data than an abstract description of it. Please have a read of this[/url].

John

todd.ayers SSCommitted Points: 1837 More actions · Answer 8

Thank you for the link to posting data. I will make sure to use it in the future. I guess I will head to an access forum and see if I can figure this out.

I dont have the ability to enter this code in a format that would benefit from a SQL standpoint. Thx anyway....

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 9

If you use DISTINCT it will remove rows from the result set where all selected columns are equal. In this case, the Stack Id is the same but the other values are not - so both the rows (for StackId 299794) will still be included.

You need something cleverer which shows the data you want for one instance of the StackId e.g. details of latest location - but this is more complicated.

If you need Access code an Access forum is better because there's no guarantee SQL Server code will work anyway.

Sean Lange SSC Guru Points: 286573 More actions · Answer 10

todd.ayers (7/10/2013)
Well.... I have four fields called Stack ID, Location, Move Date, and Move Time.
The Stack ID refers to a bundle of wood and in that column we have multiple stack id's on any given day. We move them from one location to another but if we move a bundle from one location to another in the same day, that bundle shows up twice in the query.... in two different locations. I am trying to get the query to still pull the entire list of stack id's, locations, move date etc. but only show the stack ids only once.
For instance stack id: 299794 shows in location: Plant and location: Dry Storage because it was moved twice on 7/8.
I pray this makes sense?

It makes sense from a presentation standpoint but it is not how sql works. This is not a sql server thing, the same thing will be there in Access. Each row will have a stackid associated with it. If you want prevent the duplicates from appearing on screen you will need to address it in the presentation layer, not the data layer.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/