MS SQL Most Recent Date

Hi, I would like to select a number of tables based on the most recent date and where it is inactive.  The current problem is that if the dates are equal multiple tables come up (I just want one; the first one that it finds).  I tried using DISTINCT, but it didn't seem to work.


Here's how it's set up:
Table1:  Number
Table2:  id, Table1$Number, Table2$id
Table3:  id, IsActive

Table1 has many Table2.
Table2 has one Table1.
Table2 has one Table3.
Table3 has many Table2.


Here's what I have:
Table1.Number - Table2.Date - Table3.IsActive
123 - 7/17/2009 - Y
123 - 7/13/2009 - N
123 - 7/14/2009 - N
475 - 7/14/2009 - Y
475 - 7/14/2009 - Y
475 - 7/14/2009 - N
475 - 7/14/2009 - N


Here's what I want to output:
Table1.Number - Table2.Date - Table3.IsActive
123 - 7/14/2009 - N
475 - 7/14/2009 - N

Here's what I get:
Table1.Number - Table2.Date - Table3.IsActive
123 - 7/14/2009 - N
475 - 7/14/2009 - N     ' I'd like to have just one
475 - 7/14/2009 - N


Also if I can do the query below a better way, please show how.  I want to use MAX though.
I'm using Microsoft SQL Server 2005.
Thanks.
SELECT  Table1.Name
        CONVERT(varchar,Table2.Date,101) as Date
FROM    Table1                                          LEFT OUTER JOIN
        Table2 ON Table1.Name      = Table2.Table1$Name LEFT OUTER JOIN
        Table3 ON Table2.Table3$id = Table3.id
WHERE   Table2.Date IN (
          SELECT MAX(Date)
          FROM   Table2 x
          WHERE  Table1.name     = x.Table1$name
             AND x.Table3$id     = Table3.id
             AND Table3.IsActive = 'N'
        )

Open in new window

no1leftypitcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Since you are specifically looking for IsActive = 'N', you can do this since the other values can be grouped on.
SELECT t1.Number, MAX(t2.Date) AS Date, t3.IsActive
FROM Table1 t1
INNER JOIN (
	Table2 t2 INNER JOIN Table3 t3
		ON t3.id = t2.Table3$id
) ON t2.Table1$Number = t1.Number
WHERE t3.IsActive = 'N'
GROUP BY t1.Number, t3.IsActive

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
If there is more to it than that, you can use an analytical statement with OVER keyword and ROW_NUMBER function to get the results you need given I have the joins correct.
SELECT t1.Number
, CONVERT(varchar, t2.Date, 101) AS Date
, t3.IsActive
FROM (
	SELECT t1.Number, t2.Date, t3.IsActive
	, ROW_NUMBER() OVER (PARTITION BY t1.Number ORDER BY t2.Date DESC) AS row
	FROM Table1 t1
	INNER JOIN (
		Table2 t2 INNER JOIN Table3 t3
			ON t3.id = t2.Table3$id
	) ON t2.Table1$Number = t1.Number
	WHERE t3.IsActive = 'N'
) t
WHERE t.row = 1;

Open in new window

0
no1leftypitcherAuthor Commented:
Thanks for the comments, but when I ran the query, it said that OVER is not recognized/supported and that ROW_NUMBER() is not a valid function.  I guess my version of MS SQL is too old for these; is there another way?
0
Kevin CrossChief Technology OfficerCommented:
These should work in MS SQL Server 2005, but in either case I provided this method http:#24855112, does it not work?
0
Kevin CrossChief Technology OfficerCommented:
For MS SQL Server 2000, you can simulate ROW_NUMBER() like this:
SELECT t1.Number
, CONVERT(varchar, t2.Date, 101) AS Date
, t3.IsActive
FROM (
        SELECT t1.Number, t2.Date, t3.IsActive
        , (SELECT COUNT(*) FROM Table1 INNER JOIN Table2 ON Table1$Number = Number WHERE Number = t1.Number AND Date >= t2.Date) AS row
        FROM Table1 t1
        INNER JOIN (
                Table2 t2 INNER JOIN Table3 t3
                        ON t3.id = t2.Table3$id
        ) ON t2.Table1$Number = t1.Number
        WHERE t3.IsActive = 'N'
) t
WHERE t.row = 1;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.