select max date only 1

[codrutza]

I have 3 tables, j1,j2,j3. In j1 I have the jobs, and in j2,j3 the moves. For each job there are several moves. I write
SELECT
j1.JNumber,
j3.MNumber,
j3.ADate,
other fields….

FROM j1 WITH(READUNCOMMITTED)
inner join j2 WITH(READUNCOMMITTED)
on j1.JNumber=j2.JNumber
inner join j3 WITH(READUNCOMMITTED)
on j2.MNumber=j3.MNumber

Please help me, if it's possible, to write a select statement or a script, or a stored procedure, that retrieves one record for each job with the mov in which ADate is the maximum.

So, for example, instead of
j60 ma60 01/01/2010
j60 mb60 27/01/2010
j62 mc62 01/01/2010
j62 md62 29/01/2010
j87 me87 05/02/2010
j87 mf87 12/01/2010

I have to have
j60 mb60 27/01/2010
j62 md62 29/01/2010
j87 me87 05/02/2010

Thanks

 

[bborissov]

What if you have these records:
[tt]
j60 ma60 01/01/2010
j60 mb60 27/01/2010
j60 mb60 27/01/2010
j60 mb60 27/01/2010
j60 mb60 27/01/2010

j62 mc62 01/01/2010
j62 md62 29/01/2010
j62 md62 29/01/2010
j62 md62 29/01/2010

j87 me87 05/02/2010
j87 mf87 12/01/2010
j87 mf87 12/01/2010
j87 mf87 12/01/2010
[/tt]
What do you want as a result from this?


Borislav Borissov
VFP9 SP2, SQL Server

 

[codrutza]

Thanks Borislav. The result would be the same:

j60 mb60 27/01/2010
j62 md62 29/01/2010
j87 me87 05/02/2010

and I have as well jobs with only one mov from beginning, which I forgot to say the first time

 

[gmmastros]

Try this:

; With Data As
(
  SELECT j1.JNumber,
         j3.MNumber,
         j3.ADate,
         other fields…,
         Row_Number() Over (Partition By j1.JNumber Order By j3.ADate DESC) As RowId
  FROM   j1 WITH(READUNCOMMITTED)
         inner join j2 WITH(READUNCOMMITTED)
           on j1.JNumber=j2.JNumber
         inner join j3 WITH(READUNCOMMITTED)
           on j2.MNumber=j3.MNumber
)
Select   *
From     Data
Where    RowId = 1

If this works for you, and you would like me to explain it, please let me know.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

Thanks, George, this is working. Could you explain why, please? How shall I write this code in a SP? I wrote

create PROCEDURE [dbo].[testarrival]

;With mData As
(
SELECT ....

and it gives me
Incorrect syntax near ';'.

 

[gmmastros]

You're missing an AS...

create PROCEDURE [dbo].[testarrival]
[!]AS[/!]
;With mData As
(
SELECT ....

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

The real magic is here:

Row_Number() Over (Partition By j1.JNumber Order By j3.ADate DESC) As RowId

Row_Number() will return a sequence number starting at 1 and incrementing. Partition By j1.JNumber tells it to start re-numbering every time j1.JNumber changes. The order by applies to each "group" where the group of rows would be for each unique J1.JNumber. We want to sort descending so that the most recent date has RowId = 1. We use this to filter on the outer query.

Does this make sense now?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

Excellent. Thank you very much, George.