need help with query
in [Programming SQL]
|
From: skywalker skywalker on 20 Apr 2010 13:50 Dear Guru, I need help with sql query that combine two tables, and filter. I have following tables: tblInbox, tblInboxThread, tblInbox ID, UserFrom, UserTo, vSubject, vMessage, dCreateDate tblInboxThread ID, iInboxThread, ThreadUserFrom, ThreadUserTo, vThreadMessage, dCreateDate where the iInboxThread is referring to ID of tblInbox =============================================== I would like to extract the most recent message (by max creation date) to be displayed in inbox for each inbox ID Example of table contents: tblInbox ID UserFrom UserTo vSubject vMessage dCreateDate == ======= ===== ====== ======== ========== 1 Sandy David hello Test Msg 2010-04-13 00:45:03.450 2 Mike Vince hello2 Test Msg2 2010-04-14 00:45:03.450 3 Dona Bruce hello3 Test Msg3 2010-04-15 00:45:03.450 tblThreadInbox ID iInboxThread ThreadUserFrom ThreadUserTo vThreadMessage dCreateDate == ========= ============= ========== ============= ========= 1 1 David Sandy Also another test 2010-04-16 00:45:03.450 2 1 Sandy David Ok. noted 2010-04-17 00:45:03.450 3 3 Bruce Dona Bye bye 2010-04-17 00:33:02.451 So, I need a single sql query that able to combine both table, filter and output following that have the most recent entry for each inboxID: tblInboxID FromUser ToUser Subject Message CreationDate ======= ======== ====== ======= ======= =========== 1 Sandy David hello Ok. noted 2010-04-17 00:45:03.450 2 Mike Vince hello2 Test Msg2 2010-04-14 00:45:03.450 3 Bruce Dona hello3 Bye bye 2010-04-17 00:33:02.451 Thank you very much. Regards, Sky
From: Plamen Ratchev on 20 Apr 2010 17:36 Here is how you can get the most recent messages: SELECT ID, UserFrom, UserTo, vSubject, vMessage, dCreateDate FROM ( SELECT ID, UserFrom, UserTo, vSubject, vMessage, dCreateDate, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY dCreatedDate DESC) AS rk FROM tblInbox) AS I WHERE rk = 1; To get the thread info: SELECT I.ID, ThreadUserFrom, ThreadUserTo, vSubject, vMessage, vThreadMessage, I.dCreateDate FROM ( SELECT ID, UserFrom, UserTo, vSubject, vMessage, dCreateDate, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY dCreatedDate DESC) AS rk FROM tblInbox) AS I JOIN tblInboxThread AS T ON I.ID = T.iInboxThread WHERE rk = 1; -- Plamen Ratchev http://www.SQLStudio.com
From: skywalker skywalker on 20 Apr 2010 19:46 Hi, thanks for reply. I'm using sql 2000. ROW_NUMBER() is not recognised. On Apr 21, 5:36 am, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote: > Here is how you can get the most recent messages: > > SELECT ID, UserFrom, UserTo, vSubject, vMessage, dCreateDate > FROM ( > SELECT ID, UserFrom, UserTo, vSubject, vMessage, dCreateDate, > ROW_NUMBER() OVER(PARTITION BY ID ORDER BY dCreatedDate DESC) AS rk > FROM tblInbox) AS I > WHERE rk = 1; > > To get the thread info: > > SELECT I.ID, ThreadUserFrom, ThreadUserTo, vSubject, vMessage, vThreadMessage, I.dCreateDate > FROM ( > SELECT ID, UserFrom, UserTo, vSubject, vMessage, dCreateDate, > ROW_NUMBER() OVER(PARTITION BY ID ORDER BY dCreatedDate DESC) AS rk > FROM tblInbox) AS I > JOIN tblInboxThread AS T > ON I.ID = T.iInboxThread > WHERE rk = 1; > > -- > Plamen Ratchevhttp://www.SQLStudio.com
From: Plamen Ratchev on 20 Apr 2010 21:26 On SQL Server 2000 you can try the following (note it may produce multiple rows for ID if there are max create date values that are the same): SELECT I.ID, ThreadUserFrom, ThreadUserTo, vSubject, vMessage, vThreadMessage, I.dCreateDate FROM tblInbox AS I JOIN tblInboxThread AS T ON I.ID = T.iInboxThread WHERE I.dCreateDate = (SELECT MAX(B.dCreateDate) FROM tblInbox AS B WHERE B.ID = I.ID); -- Plamen Ratchev http://www.SQLStudio.com
From: skywalker skywalker on 20 Apr 2010 23:26
Dear Plamen, The result is not correct. Regards, Sky On Apr 21, 9:26 am, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote: > On SQL Server 2000 you can try the following (note it may produce multiple rows for ID if there are max create date > values that are the same): > > SELECT I.ID, ThreadUserFrom, ThreadUserTo, vSubject, vMessage, vThreadMessage, I.dCreateDate > FROM tblInbox AS I > JOIN tblInboxThread AS T > ON I.ID = T.iInboxThread > WHERE I.dCreateDate = (SELECT MAX(B.dCreateDate) > FROM tblInbox AS B > WHERE B.ID = I.ID); > > -- > Plamen Ratchevhttp://www.SQLStudio.com |