no1leftypitcher
asked on
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.
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'
)
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;
ASKER
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?
These should work in MS SQL Server 2005, but in either case I provided this method http:#24855112, does it not work?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window