Help with store proc query

Question

Post reply

Help with store proc query

Patrick.I

Hall of Fame

Points: 3105
More actions
November 17, 2005 at 5:08 pm

#90826

I have 2 tables newPostSR and replySR
In my Forum App i'm tring to get the last reply name and date to the LAST POST column.
But i noticed my "ISNULL(max(ReplySR.Name),'-') as r_name," returns a good r_date but doesn't return
the right r_name to it.
Any help?

CREATE Procedure forumpost
(
    @VM nvarchar(255)
)
AS
SELECT newPostSR.postid,newPostSR.subject, newPostSR.name, newPostSR.replies, newPostSR.views, newPostSR.dt,
isnull(max(ReplySR.ReplyID),'-') as ReplyID,
ISNULL(max(ReplySR.Name),'-') as r_name,
ISNULL(max(ReplySR.dt),'-') as r_date
       FROM newPostSR
LEFT JOIN ReplySR ON( newPostSR.PostID = ReplySR.PostID)
LEFT JOIN( SELECT PostID as postid, MAX(replyid) AS LatestDate
                           FROM ReplySR
                          GROUP BY PostID) LatestReply
          ON( ReplySR.PostID = LatestReply.PostID
                AND ReplySR.replyid = LatestReply.LatestDate)
WHERE newPostSR.PostID < @VM
group by newPostSR.postid,newPostSR.subject, newPostSR.name, newPostSR.replies, newPostSR.views, newPostSR.dt
ORDER BY newpostSR.PostId DESC
GO

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Post DDL for tables involved, along with a few sample rows of data for each table and your desired output.

Provide it in 'script' form so that it is easy to copy and paste into QA.

/Kenneth

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 2

Try:

SELECT newPostSR.postid,newPostSR.subject,newPostSR.nam

, newPostSR.replies,newPostSR.views,newPostSR.dt

, ISNULL(ReplySR.ReplyID,'-') AS ReplyID

, ISNULL(ReplySR.Name,'-') AS r_name

, ISNULL(ReplySR.dt,'-') AS r_date

FROM newPostSR

LEFT JOIN (SELECT R.PostID,R.ReplyID,R.Name,R.dt

FROM ReplySR AS R

LEFT JOIN (SELECT PostID, MAX(replyid) AS replyid

FROM ReplySR GROUP BY PostID) AS LatestReply

ON ReplySR.PostID = LatestReply.PostID

AND ReplySR.replyid = LatestReply.replyid) AS ReplySR

GROUP BY R.PostID,R.ReplyID,R.Name,R.dt

ON newPostSR.PostID = ReplySR.PostID

WHERE newPostSR.PostID < @VM

GROUP BY newPostSR.postid,newPostSR.subject,newPostSR.nam

,newPostSR.replies,newPostSR.views,newPostSR.dt

,ReplySR.PostID,ReplySR.ReplyID,ReplySR.Name,ReplySR.dt

ORDER BY newpostSR.PostId DESC

Without data, it is tough to debug.

Andy

Patrick.I Hall of Fame Points: 3105 More actions · Answer 3

Patrick.I

Hall of Fame

Points: 3105

November 19, 2005 at 6:50 am

#604791

Thx guys.

Will get back to you.

Patrick

Patrick.I Hall of Fame Points: 3105 More actions · Answer 4

Thanks Kenneth and David for the reply here is my DDL for the two tables

CREATE TABLE [newpostSR] (

[postid] [int] IDENTITY (1, 1) NOT NULL ,

[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[subject] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

127.0.0.1 [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[dt] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[message] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[replies] [smallint] NOT NULL CONSTRAINT [DF_newpostSR_replies] DEFAULT (0),

[views] [int] NOT NULL CONSTRAINT [DF_newpostSR_views] DEFAULT (0)

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [replySR] (

[replyid] [int] IDENTITY (1, 1) NOT NULL ,

[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[subject] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

127.0.0.1 [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[dt] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[message] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[postid] [int] NOT NULL CONSTRAINT [DF_replySR_postid] DEFAULT (0)

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Thanks

Patrick

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Great, almost there.

You need to supply a few rows of data for each table that demonstrates the problem you're having, and a display of your desired output from those sample rows.

/Kenneth

Vladan SSC-Insane Points: 22072 More actions · Answer 6

I'm not able to put the correct code together without some sample data, but the problem is clear : your code uses 3 times MAX, for each value separately. Each MAX is therefore evaluated separately and independently; that means, instead of finding id, time and name of the lastest post, you find highest ID of all replies, latest time of all replies (which will probably be from the same post as highest ID in 99.9%, but you can't rely on it anyway) and highest name from all authors of replies - this will be wrong most of the time.

What you need is to select name and time for the reply with highest ID... try David's code whether it helps. There may be some typo or misinterpretation, but generally it does what you need : joins the table ReplySR repeatedly to find the ID of last reply for each post first, and then in second step retrieves time and name that belongs to that ID.

HTH, Vladan

Vladan SSC-Insane Points: 22072 More actions · Answer 7

I found some more time to juggle with it, here is the result. Mark that the GROUP BY clause (and MAX) is not necessary in the actual select. All grouping is done "inside", when finding the last reply ID. I omitted the @VM parameter, since I had no info about it - you can add it back easily.

insert into newpostSR ([name], subject) values ('Carl','post1')

insert into newpostSR ([name], subject) values ('JOHN','post2')

insert into newpostSR ([name], subject) values ('Lina','post3')

insert into replySR ([name], subject, dt, postid) values ('Samantha', 'good job', '20051010', 1)

insert into replySR ([name], subject, dt, postid) values ('Carl', 'Wow', '20051011', 1)

insert into replySR ([name], subject, dt, postid) values ('Dick', 'Hey hey', '20051011', 1)

insert into replySR ([name], subject, dt, postid) values ('Jane', 'huh?', '20051012', 1)

insert into replySR ([name], subject, dt, postid) values ('Audrey', 'thx', '20051012', 1)

SELECT newPostSR.postid,newPostSR.subject, newPostSR.name, newPostSR.replies, newPostSR.views, newPostSR.dt,

isnull(rep.ReplyID,'-') as ReplyID,

ISNULL(rep.Name,'-') as r_name,

ISNULL(rep.dt,'-') as r_date

FROM newPostSR

LEFT JOIN

(SELECT PostID, MAX(replyid) as LatestID

FROM ReplySR GROUP BY PostID) AS Q ON (newPostSR.PostID = Q.PostID)

LEFT JOIN ReplySR rep ON Q.PostID = rep.PostID AND Q.LatestID = rep.replyID

-- WHERE newPostSR.PostID < @VM

ORDER BY newpostSR.PostId DESC

RESULT:

3 post3 Lina 0 0 NULL 0 - -

2 post2 JOHN 0 0 NULL 0 - -

1 post1 Carl 0 0 NULL 5 Audrey 20051012

Patrick.I Hall of Fame Points: 3105 More actions · Answer 8

Thanks Vladan for the reply and everybody else who has been replying me.

And after testing your Stored Proc it did the job.

I have tested some rows against r_name and r_date and i'm getting the right data.

Actually the funniest thing is that your Stored proc was much more less..

Will test it more and feed you back..Thanks