Results 1 to 4 of 4

Thread: help with a query/view

  1. #1
    Join Date
    Apr 2005
    Posts
    7

    help with a query/view

    Hi all!

    I have a table that looks like this:

    m_id | s_id1 | s_id2 | dt
    m1 | 1 | 2 | 2001-01-01
    m2 | 3 | 2 | 2001-01-02
    m3 | 4 | 1 | 2001-01-03
    m4 | 2 | 3 | 2001-01-04
    m5 | 1 | 2 | 2001-01-05
    m6 | 5 | 3 | 2001-01-06
    ...

    I need to create a view that displays ONE m_id for every s_id, and that m_id should be the latest one...

    ie:
    s_id | m_id
    1 | m5
    2 | m5
    3 | m6
    4 | m3
    5 | m6

    I've been trying hard for a while, but I simply can't get it do display only one row per s_id...

    any helponthis one would be greatly apreciated...

    cheers.

  2. #2
    Join Date
    Feb 2003
    Posts
    1,048
    If by s_id, you mean speciafically s_id1 or s_id2 then:


    Select s_id1 As s_id, m_id
    From theTable As tT
    Inner Join (Select s_id1, Max(dt) as dt
    From theTable
    Group By s_id1) As tT2 On tT2.s_id1 = tT.s_id1 And tT2.dt = tT.dt

  3. #3
    Join Date
    Apr 2005
    Posts
    7
    thanks rawhide...

    the idea was to get the s_id and the latest m_id independently of if the s_id as in the s_id1 or the s_id2 column...

  4. #4
    Join Date
    Feb 2003
    Posts
    1,048
    That's a little harder.

    Step 1, combine the two columns into 1:

    Declare @myTable Table (s_id int, dt datetime)

    Insert Into @myTable
    Select s_id1, dt
    From theTable
    Union
    Select s_id2, dt
    From theTable

    Step 2, do what we did earlier:

    Select s_id1 As s_id, m_id
    From theTable As tT
    Inner Join (Select s_id, Max(dt) as dt
    From @myTable
    Group By s_id) As tT2 On tT2.s_id1 = tT.s_id1 And tT2.dt = tT.dt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •