• all threads without replies;
• for threads with replies, the last reply.

A.ID,
	A.connected,
	B.id,
	B.connected,
	A.datum,
	B.datum_update,
	A.title,
	A.author,
	B.message,
	B.last_replies
FROM
	forums a
LEFT OUTER JOIN forums b ON a.id = b.connected
LEFT 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 = 0
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        |
| 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

SELECT * FROM 
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY ID ORDER BY datum_update DESC) AS RN
	FROM
		Forums
) WHERE RN = 1;

SELECT * FROM 
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY ID ORDER BY datum_update DESC) AS RN
	FROM
		Forums
) WHERE RN = 1;

ERROR [42000] - [SQL Server] Incorrect syntax near the keyword 'WHERE'.

-- ----------------------------
-- Table structure for [dbo].[forums]
-- ----------------------------
DROP TABLE [dbo].[forums]
GO
CREATE 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');
GO
INSERT 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');
GO
INSERT 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');
GO
INSERT 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');
GO
INSERT 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

...
	FROM
		Forums
) AS s WHERE RN = 1;

quote:

---

Can there be more than one level in the thread? For example, can there be another row which has connected = 394?

---

id	connected	datum			datum_updated		title		author		message			last_replies	RN
395	392		24/01/2013 13.17	24/01/2013 1.17		help me		Sammy		regards			Sammy		1
396	0		16/02/2013 21.28	16/02/2013 21.28	new thread	Rudy		this is new thread	Rudy		1
397	0		18/02/2013 21.35	18/02/2013 21.36	post new thread	swampBoogie	this is my new thread	swampBoogie	1

id	connected	datum			datum_updated		title		author		message			last_replies	RN
392	0		16/08/2012 11.19	16/08/2012 11.08	help me		Sandra		hello my friend		Sandra		1
394	392		24/08/2012 12.15	24/08/2012 0.08		help me		admin		hi there		admin		1
395	392		24/01/2013 13.17	24/01/2013 1.17		help me		Sammy		regards			Sammy		1
396	0		16/02/2013 21.28	16/02/2013 21.28	new thread	Rudy		this is new thread	Rudy		1
397	0		18/02/2013 21.35	18/02/2013 21.36	post new thread	swampBoogie	this is my new thread	swampBoogie	1
SELECT * FROM 
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY ID ORDER BY datum_updated DESC) AS RN
	FROM
		Forums
) AS s WHERE RN = 1;

SELECT * FROM 
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY IDtitle ORDER BY datum_updated DESC) AS RN
	FROM
		Forums
) AS s WHERE RN = 1;

quote:

---

Originally posted by visakh16

should be

SELECT * FROM 
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY IDtitle ORDER BY datum_updated DESC) AS RN
	FROM
		Forums
) AS s WHERE RN = 1;

i guess

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---