Select most recent record


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Select most recent record

  1. #1
    Rick Guest

    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.

  2. #2
    Michael Levy Guest

    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



  3. #3
    DaveSatz Guest

    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.




  4. #4
    JeffB Guest

    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



  5. #5
    Eoin Beck Guest

    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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center