Pulling distinct values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Pulling distinct values

Author

Topic

rdave123
Starting Member

1 Post

Posted - 2015-03-09 : 19:15:23

I'm working with inventory in a large distribution center and I'm running a simple query to pull all transactions for the year for a given part number, along with the corresponding location. This is useful for finding parts that go missing, because you can see everywhere it has been before. The resulting table has 2 columns: LOCATION_ID and TRANSACTION_DATE:

SELECT LOCATION_ID,TRANSACTION_DATE
FROM WAREHOUSE_TRANSACTIONS
WHERE PART_ID = ' '
AND TRANS_DATE BETWEEN '2015-01-01' AND '2015-12-31'
ORDER BY LOCATION_ID,TRANSACTION_DATE DESC

So far so good, but the location column contains duplicates because there can be 100s of transactions. I need it to pull distinct locations and their most recent transaction date. This would give me a list of distinct past locations and the date of the last time the part was there.

I've tried using DISTINCT and MAX statements but with no luck. I hope this makes sense. Thanks for any help.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-10 : 06:51:39

[code]
SELECT LOCATION_ID, MAX(TRANSACTION_DATE) AS TRANSACTION_DATE
FROM WAREHOUSE_TRANSACTIONS
WHERE PART_ID = ' '
AND TRANS_DATE >= '20150101'
AND TRANS_DATE < '20160101'
GROUP BY LOCATION_ID
ORDER BY LOCATION_ID;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources