Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
mrivero1961
Starting Member
7 Posts |
Posted - 2013-02-19 : 11:54:05
|
Hello everyone.Thanks all in advance for any help and suggestions.I've the table `forums` where register threads, I need in output: - all threads without replies;
- 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: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 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-19 : 12:18:21
|
If I understood your requirements correctly, something like shown below should workSELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY datum_update DESC) AS RN FROM Forums) WHERE RN = 1; |
|
|
mrivero1961
Starting Member
7 Posts |
Posted - 2013-02-19 : 12:54:20
|
thank you for help, but in the query I've error: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]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 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-19 : 12:59:39
|
I had missed an alias - that is why you got a parsing error. See below... FROM Forums) AS s WHERE RN = 1; But, that is not going to give you the results you are looking for if there should be only three rows in the desired output.Can there be more than one level in the thread? For example, can there be another row which has connected = 394? |
|
|
mrivero1961
Starting Member
7 Posts |
Posted - 2013-02-19 : 13:17:23
|
quote: Can there be more than one level in the thread? For example, can there be another row which has connected = 394?
No, not possible.This is the output correct:id connected datum datum_updated title author message last_replies RN395 392 24/01/2013 13.17 24/01/2013 1.17 help me Sammy regards Sammy 1396 0 16/02/2013 21.28 16/02/2013 21.28 new thread Rudy this is new thread Rudy 1397 0 18/02/2013 21.35 18/02/2013 21.36 post new thread swampBoogie this is my new thread swampBoogie 1 with your suggestion I've:id connected datum datum_updated title author message last_replies RN392 0 16/08/2012 11.19 16/08/2012 11.08 help me Sandra hello my friend Sandra 1394 392 24/08/2012 12.15 24/08/2012 0.08 help me admin hi there admin 1395 392 24/01/2013 13.17 24/01/2013 1.17 help me Sammy regards Sammy 1396 0 16/02/2013 21.28 16/02/2013 21.28 new thread Rudy this is new thread Rudy 1397 0 18/02/2013 21.35 18/02/2013 21.36 post new thread swampBoogie this is my new thread swampBoogie 1SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY datum_updated DESC) AS RN FROM Forums) AS s WHERE RN = 1; |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-19 : 13:20:43
|
should beSELECT * 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 MVPhttp://visakhm.blogspot.com/ |
|
|
mrivero1961
Starting Member
7 Posts |
Posted - 2013-02-19 : 13:25:51
|
quote: Originally posted by visakh16 should beSELECT * 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 MVPhttp://visakhm.blogspot.com/
thank you, this version working! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-20 : 01:21:22
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|