InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 20:15:51 GMT20http://www.sqlservercentral.com/Forums/Topic1421741-1292-1.aspx[quote][b]Kingston Dhasian (2/20/2013)[/b][hr]This should help[code="sql"]WHERE a.connected = 0 AND ( dt.connected IS NOT NULL OR b.id IS NULL )[/code][/quote]Thank you very much, now working!!! :-D[code]SELECT A.ID, A.connected, B.id, B.connected, A.datum, B.datum_update, A.title, A.author, B.message, B.last_repliesFROM forums aLEFT OUTER JOIN forums b ON a.id = b.connectedLEFT OUTER JOIN ( SELECT connected, max(datum_update) AS max_date FROM forums GROUP BY connected) dt ON (b.connected, b.datum_update) = (dt.connected, dt.max_date)WHERE a.connected = 0AND ( dt.connected IS NOT NULL OR b.id IS NULL)ORDER BY dt.max_date DESC;+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+---------+--------------+| ID | connected | id | connected | datum | datum_update | title | author | message | last_replies |+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+---------+--------------+| 392 | 0 | 395 | 392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me | Sandra | regards | Sammy || 396 | 0 | NULL | NULL | 2013-02-16 21:28:42 | NULL | new thread | Rudy | NULL | NULL || 397 | 0 | NULL | NULL | 2013-02-18 21:35:59 | NULL | post new thread | swampBoogie | NULL | NULL |+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+---------+--------------+3 rows in set[/code]Wed, 20 Feb 2013 07:53:24 GMTmrivero1961http://www.sqlservercentral.com/Forums/Topic1421741-1292-1.aspxThis should help[code="sql"]WHERE a.connected = 0 AND ( dt.connected IS NOT NULL OR b.id IS NULL )[/code]Even the below code will solve your requirement if you are using SQL Server 2005+[code="sql"]SELECT *FROM forums AS aLEFT OUTER JOIN ( SELECT ROW_NUMBER() OVER ( PARTITION BY connected ORDER BY datum_updated DESC ) AS RN, * FROM forums ) b ON a.id = b.connected AND b.RN = 1WHERE a.connected = 0[/code]Edit: Added alternate codeWed, 20 Feb 2013 07:45:38 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic1421741-1292-1.aspx[quote][b]Kingston Dhasian (2/20/2013)[/b][hr]change your WHERE Clause like below[code="sql"] WHERE A.connected = 0 AND dt.connected IS NOT NULL [/code][/quote]thank you for help, but the output is incorrect:[code]SELECT A.ID, A.connected, B.id, B.connected, A.datum, B.datum_update, A.title, A.author, B.message, B.last_repliesFROM forums aLEFT OUTER JOIN forums b ON a.id = b.connectedLEFT OUTER JOIN ( SELECT connected, max(datum_update) AS max_date FROM forums GROUP BY connected) dt ON (b.connected, b.datum_update) = (dt.connected, dt.max_date)WHERE A.connected = 0AND dt.connected IS NOT NULLORDER BY dt.max_date DESC;+-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+| ID | connected | id | connected | datum | datum_update | title | author | message | last_replies |+-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+| 392 | 0 | 395 | 392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me | Sandra | regards | Sammy |+-----+-----------+-----+-----------+---------------------+---------------------+---------+--------+---------+--------------+1 row in set[/code]I need this output:[code]+-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+| id | datum | datum_update | title | author | message | last_replies | connected |+-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+| 395 | 2013-01-24 13:17:27 | 2013-01-24 01:17:00 | help me | Sammy | regards | Sammy | NULL || 396 | 2013-02-16 21:28:42 | 2013-02-16 21:28:49 | new thread | Rudy | this is new thread | Rudy | NULL || 397 | 2013-02-18 21:35:59 | 2013-02-18 21:36:04 | post new thread | swampBoogie | this is my new thread | swampBoogie | NULL |+-----+---------------------+---------------------+-----------------+-------------+-----------------------+--------------+-----------+[/code]Wed, 20 Feb 2013 07:30:32 GMTmrivero1961http://www.sqlservercentral.com/Forums/Topic1421741-1292-1.aspxchange your WHERE Clause like below[code="sql"] WHERE A.connected = 0 AND dt.connected IS NOT NULL [/code]Wed, 20 Feb 2013 05:11:45 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic1421741-1292-1.aspxtable `forums` where register threadsHello everyone.Thanks all in advance for any help and suggestions.I've the table `forums` where register threads, I need in output:1) all threads without replies;2) for threads with replies, the last reply.I tried this query but is a problem: in output not are visible the new threads without replies.This version where use LEFT OUTER JOIN return this new output, the row # 2 -b.id = 394- is too ...Please check this:[code] SELECT A.ID, A.connected, B.id, B.connected, A.datum, B.datum_update, A.title, A.author, B.message, B.last_repliesFROM forums aLEFT OUTER JOIN forums b ON a.id = b.connectedLEFT OUTER JOIN ( SELECT connected, max(datum_update) AS max_date FROM forums GROUP BY connected) dt ON (b.connected, b.datum_update) = (dt.connected, dt.max_date)WHERE A.connected = 0ORDER BY dt.max_date DESC;+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+| ID | connected | id | connected | datum | datum_update | title | author | message | last_replies |+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+| 392 | 0 | 395 | 392 | 2012-08-16 11:19:16 | 2013-01-24 01:17:00 | help me | Sandra | regards | Sammy || 392 | 0 | 394 | 392 | 2012-08-16 11:19:16 | 2012-08-24 00:08:00 | help me | Sandra | hi there | admin || 396 | 0 | NULL | NULL | 2013-02-16 21:28:42 | NULL | new thread | Rudy | NULL | NULL || 397 | 0 | NULL | NULL | 2013-02-18 21:35:59 | NULL | post new thread | swampBoogie | NULL | NULL |+-----+-----------+------+-----------+---------------------+---------------------+-----------------+-------------+----------+--------------+4 rows in set-- ------------------------------ Table structure for [dbo].[forums]-- ----------------------------DROP TABLE [dbo].[forums]GOCREATE TABLE [dbo].[forums] ([id] int NULL ,[connected] int NULL ,[datum] datetime NULL ,[datum_updated] datetime NULL ,[title] nvarchar(255) NULL ,[author] nvarchar(255) NULL ,[message] nvarchar(255) NULL ,[last_replies] nvarchar(255) NULL )GO-- ------------------------------ Records of forums-- ----------------------------INSERT INTO [dbo].[forums] ([id], [connected], [datum], [datum_updated], [title], [author], [message], [last_replies]) VALUES (N'392', N'0', N'2012-08-16 11:19:16.000', N'2012-08-16 11:08:00.000', N'help me', N'Sandra', N'hello my friend', N'Sandra');GOINSERT INTO [dbo].[forums] ([id], [connected], [datum], [datum_updated], [title], [author], [message], [last_replies]) VALUES (N'394', N'392', N'2012-08-24 12:15:27.000', N'2012-08-24 00:08:00.000', N'help me', N'admin', N'hi there', N'admin');GOINSERT INTO [dbo].[forums] ([id], [connected], [datum], [datum_updated], [title], [author], [message], [last_replies]) VALUES (N'395', N'392', N'2013-01-24 13:17:27.000', N'2013-01-24 01:17:00.000', N'help me', N'Sammy', N'regards', N'Sammy');GOINSERT INTO [dbo].[forums] ([id], [connected], [datum], [datum_updated], [title], [author], [message], [last_replies]) VALUES (N'396', N'0', N'2013-02-16 21:28:42.000', N'2013-02-16 21:28:49.000', N'new thread', N'Rudy', N'this is new thread', N'Rudy');GOINSERT INTO [dbo].[forums] ([id], [connected], [datum], [datum_updated], [title], [author], [message], [last_replies]) VALUES (N'397', N'0', N'2013-02-18 21:35:59.000', N'2013-02-18 21:36:04.000', N'post new thread', N'swampBoogie', N'this is my new thread', N'swampBoogie');GO[/code]Tue, 19 Feb 2013 10:00:53 GMTmrivero1961