-
Select most recent record
I need to select the most recent record for a work order (identified by WOP_WO_ID)
with who is assigned to the action (identified by WOP_WHO). Sample data is
below.
WOP_ID WOP_WO_ID WOP_DATE WOP_WHO WOP_ACTION
34333 5431 22-JAN-2001 10:22 203 Action sdkk
34334 5431 23-JAN-2001 11:10 256 Action kj9 n984j
34335 5431 24-JAN-2001 09:07 223 Action 234ff3
34336 5532 24-JAN-2001 08:43 298 Action vrf3
34337 5532 24-JAN-2001 09:54 209 Action 54f3
The result I need is:
WOP_WO_ID WOP_DATE WOP_WHO
5431 24-JAN-2001 223
5532 24-JAN-2001 209
How do I do it?
Thanks all.
-
Re: Select most recent record
Straight off the cuff.
SELECT WOP_WO_ID, WOP_DATE, WOP_WHO
FROM the_table
WHERE wop_date = (
SELECT MAX(wop_date)
FROM the_table t
WHERE t.wop_wo_id = the_table.wop_wo_id)
-Mike
--
Michael Levy MCDBA, MCSD, MCT
michaell@gasullivan.com
-
Re: Select most recent record
something like this logic:
SELECT id, crdate, type, name
FROM sysobjects o
WHERE crdate = ( SELECT max(o2.crdate)
FROM sysobjects o2
WHERE o.type = o2.type)
ORDER BY crdate, type, name
if crdate are equal to the millisec, more than 1 row shows up for the type
--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group only)
-----------------------------------------------------------------
"Rick" <rick.pollockNOSPAM@au.pwcglobal.com> wrote in message
news:3a6e4a0b$1@news.devx.com...
>
> I need to select the most recent record for a work order (identified by
WOP_WO_ID)
> with who is assigned to the action (identified by WOP_WHO). Sample data is
> below.
>
> WOP_ID WOP_WO_ID WOP_DATE WOP_WHO WOP_ACTION
> 34333 5431 22-JAN-2001 10:22 203 Action sdkk
> 34334 5431 23-JAN-2001 11:10 256 Action kj9 n984j
> 34335 5431 24-JAN-2001 09:07 223 Action 234ff3
> 34336 5532 24-JAN-2001 08:43 298 Action vrf3
> 34337 5532 24-JAN-2001 09:54 209 Action 54f3
>
> The result I need is:
> WOP_WO_ID WOP_DATE WOP_WHO
> 5431 24-JAN-2001 223
> 5532 24-JAN-2001 209
>
> How do I do it?
>
> Thanks all.
-
Re: Select most recent record
"Rick" <rick.pollockNOSPAM@au.pwcglobal.com> wrote:
>
>I need to select the most recent record for a work order (identified by
WOP_WO_ID)
>with who is assigned to the action (identified by WOP_WHO). Sample data
is
>below.
>
>WOP_ID WOP_WO_ID WOP_DATE WOP_WHO WOP_ACTION
>34333 5431 22-JAN-2001 10:22 203 Action sdkk
>34334 5431 23-JAN-2001 11:10 256 Action kj9 n984j
>34335 5431 24-JAN-2001 09:07 223 Action 234ff3
>34336 5532 24-JAN-2001 08:43 298 Action vrf3
>34337 5532 24-JAN-2001 09:54 209 Action 54f3
>
>The result I need is:
Rick,
>WOP_WO_ID WOP_DATE WOP_WHO
>5431 24-JAN-2001 223
>5532 24-JAN-2001 209
>
>How do I do it?
>
>Thanks all.
Select wop_wo_id, wop_date, wo_who from TABLE
where (select max(wop_date) from TABLE)
Good luck!
JeffB
-
Re: Select most recent record
Rick,
I think someone may have given you this answer already, but here's a query
that should
work for you
SELECT
T1.WOP_WO_ID
, T1.WOP_DATE
, T1.WOP_WHO
FROM TABLE T1
WHERE T1.WOP_DATE = (SELECT
MAX (T2.WOP_DATE)
FROM TABLE T2
WHERE T2.WOP_WO_ID = T1.WOP_WO_IP)
Hope that helps !
Good luck,
Eoin Beck
"Rick" <rick.pollockNOSPAM@au.pwcglobal.com> wrote:
>
>I need to select the most recent record for a work order (identified by
WOP_WO_ID)
>with who is assigned to the action (identified by WOP_WHO). Sample data
is
>below.
>
>WOP_ID WOP_WO_ID WOP_DATE WOP_WHO WOP_ACTION
>34333 5431 22-JAN-2001 10:22 203 Action sdkk
>34334 5431 23-JAN-2001 11:10 256 Action kj9 n984j
>34335 5431 24-JAN-2001 09:07 223 Action 234ff3
>34336 5532 24-JAN-2001 08:43 298 Action vrf3
>34337 5532 24-JAN-2001 09:54 209 Action 54f3
>
>The result I need is:
>WOP_WO_ID WOP_DATE WOP_WHO
>5431 24-JAN-2001 223
>5532 24-JAN-2001 209
>
>How do I do it?
>
>Thanks all.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|