Last movements of rolls - 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 2000 Forums

SQL Server Development (2000)

Last movements of rolls

Author

Topic

jeusdi
Starting Member

27 Posts

Posted - 2006-07-03 : 05:04:52

Hello, I would like me know how can I obtain the follow consult:

I have a table that contains a historic movement of rolls:

DATE | ROLL | REASON | LONG
1/2/2000 | 0001 | XXX | 30 m
1/2/2001 | 0001 | XXX | 21 m
3/2/1543 | 0002 | YYY | 23 m
4/2/1983 | 0002 | DGF | 18 m

I want obtain this:
DATE | ROLL | REASON | LONG
1/2/2001 | 0001 | XXX | 21 m
4/2/1983 | 0002 | DGF | 18 m

I want obtain the last movement of each roll. Can you help me, please, Thanks you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-03 : 05:07:41

Select * from yourTable
where long in (select min(long) from yourTable group by ROLL)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-03 : 05:30:23

"I want obtain the last movement of each roll"
On what basis you determine the last movement for ROLL 0002 is "4/2/1983 | 0002 | DGF | 18 m" and not the other record "3/2/1543 | 0002 | YYY | 23 m" ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-03 : 06:12:38

Seems you onto it, madhivanan.

SELECT     yt.Roll, yt.Date, yt.Reason, yt.Long
FROM       YourTable yt
INNER JOIN (SELECT Roll, MAX(Date) md FROM YourTable GROUP BY Roll) z ON z.Roll = yt.Roll AND z.md = yt.Date

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-03 : 06:21:32

quote:
Originally posted by khtan

"I want obtain the last movement of each roll"
On what basis you determine the last movement for ROLL 0002 is "4/2/1983 | 0002 | DGF | 18 m" and not the other record "3/2/1543 | 0002 | YYY | 23 m" ?

KH

Oh dear ! How is it possible that i read the data wrongly

.
As a punishment to myself, I shall not post at all for the next 5000000 ms.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-03 : 06:23:57

Or see the query below. But there is still the issue of the 1543 'date' which is not valid for the date datatype (as khtan alludes). jeusdi - was this just a typo, or is it something we need to be concerned about?

--data
declare @t table (DATE datetime, ROLL varchar(10), REASON varchar(10), LONG varchar(10))
insert @t
          select '1/2/2000', '0001', 'XXX', '30 m'
union all select '1/2/2001', '0001', 'XXX', '21 m'
union all select '3/2/1943', '0002', 'YYY', '23 m' --changed so date is valid for datatype
union all select '4/2/1983', '0002', 'DGF', '18 m'

--calculation
select * from @t a where date = (select max(date) from @t where roll = a.roll) order by roll

/*results
DATE                                                   ROLL       REASON     LONG       
------------------------------------------------------ ---------- ---------- ---------- 
2001-02-01 00:00:00.000                                0001       XXX        21 m
1983-02-04 00:00:00.000                                0002       DGF        18 m
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-03 : 06:26:39

quote:
Oh dear ! How is it possible that i read the data wrongly .

Ah! I see. I thought you were questioning the '1543' date, and I was waiting for the response before posting.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-03 : 06:41:54

quote:
Originally posted by khtanAs a punishment to myself, I shall not post at all for the next 5000000 ms.

How many timeouts are you allowed to have within the next 1 hour, 23 minutes and 20 seconds?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-03 : 09:58:31

I am back

"How many timeouts are you allowed to have within the next 1 hour, 23 minutes and 20 seconds?"
Just one

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources