Select most recent record

[Rick]

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.

[Michael Levy]

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

[DaveSatz]

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.

[JeffB]

"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

[Eoin Beck]

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.