-
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.
-
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
-
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...
-
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
-
Forum Rules
|
|