Help on 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 2012 Forums

Transact-SQL (2012)

Help on Distinct Values

Author

Topic

stuwannop
Starting Member

3 Posts

Posted - 2014-10-24 : 09:38:52

Hi Guys

First time poster hope you can help. I have a list of "item codes" in a sales line table and a list of posting dates in a "sales header" table. Many sales lines (items) may belong to a sales header.

What I am trying to do is return the most recent time each sales item was last posted/used. I have written the following code but my items are not distinct and I want the output to be a list of unqiue items with the most recent post date.

SELECT DISTINCT
Salesline.[No_] as item,
(SELECT TOP 1 Salesheader.[Posting Date]
FROM [TSG GROUP$Sales Header] as Salesheader
WHERE Salesheader.[No_] = Salesline.[Document No_]
ORDER BY Salesheader.[Posting Date] DESC) as lastpostdate

FROM [TSG GROUP$Sales Line] as Salesline

ORDER BY lastpostdate DESC

Never underestimate the power of stupid people in large groups...

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-24 : 10:03:45

If you have a one-to-one correspondence between rows in sales line table and sales header table, then what you need is something like this:

SELECT [Posting Date], item
FROM
(
	SELECT
		Salesheader.[Posting Date],
		Salesline.[No_]	 AS item,
		ROW_NUMBER() OVER (PARTITION BY Salesheader ORDER BY [Posting Date] DESC) AS RN
	FROM
		[TSG GROUP$Sales Header] AS Salesheader
		INNER JOIN [TSG GROUP$Sales Line] AS Salesline
			ON Salesheader.[No_] = Salesline.[Document No_]
)s;

Usually a sales header table would have one record and sales line table would have one or more records for each row in the sales header table. In your case that does not seem to be the case.

stuwannop
Starting Member

3 Posts

Posted - 2014-10-24 : 10:18:24

Hi James

Thanks for the reply.

Referring to your last paragraph the Sales Header has one record (which contains posting date) and the sales line has one or more associated with it which is where I am having the issue.

I thought if I did a sub select only selecting the first (i.e. Most recent) posting date from the header it would get me what I want. It returns lots of duplicate item references though and I thought the distinct clause would take care of that so not sure what I've done wrong.

Never underestimate the power of stupid people in large groups...

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2014-10-24 : 11:56:48

I think I understand. You have items that are in multiple orders. You want to know when the last time that item was ordered across all orders.


SELECT 
Salesline.[No_] as item,
MAX(Salesheader.[Posting Date]) AS lastpostdate
FROM [TSG GROUP$Sales Line] as Salesline
JOIN [TSG GROUP$Sales Header] as Salesheader
ON Salesheader.[No_] = Salesline.[Document No_]
GROUP BY Salesline.[No_]
ORDER BY lastpostdate DESC

If this is not the case, can you post some sample data for the tables and your expected results?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources