Select strategy

19 May 2006 6:27 PM

nathan001

Strategy should I use the following to select the last posting in my
forum group
in order to get the last poster I use the function that follows or
should I use the select at end that subqueries everything?.
view=
SELECT     TOP 100 PERCENT dbo.tposts.thread, MAX(dbo.posts.pid) AS
lastp, dbo.forums_S_lastby(MAX(dbo.posts.pid)) AS poster,
                          (SELECT     dateof
                            FROM          posts
                            WHERE      pid = MAX(dbo.posts.pid)) AS
dateof, GETDATE() AS timenow, dbo.tposts.fid,
                          (SELECT     COUNT(pid) - 1
                            FROM          posts
                            WHERE      tid = tposts.tid) AS replies,
dbo.tposts.tid, dbo.tposts.views, dbo.users.uname
FROM         dbo.posts INNER JOIN
                      dbo.tposts ON dbo.posts.tid = dbo.tposts.tid
INNER JOIN
                      dbo.users ON dbo.tposts.uid = dbo.users.uid
GROUP BY dbo.tposts.thread, dbo.tposts.fid, dbo.tposts.tid,
dbo.tposts.views, dbo.users.uname
ORDER BY
                          (SELECT     dateof
                            FROM          posts
                            WHERE      pid = MAX(dbo.posts.pid)) DESC

where dbo.forums_S_lastby

CREATE FUNCTION dbo.forums_S_lastby
    (
        @pid int
    )
RETURNS varchar(50)
AS
    BEGIN
        declare @lastby varchar(50)
        set @lastby='-'
        if @pid is not null begin
        SELECT @lastby=     users.uname
        FROM         users INNER JOIN
                              posts ON users.uid = posts.uid
        WHERE     (posts.pid = @pid) end
        /* sql statement ... */
    RETURN @lastby
    END

view=
SELECT     TOP 100 PERCENT dbo.tposts.thread, MAX(dbo.posts.pid) AS
lastp,
                          (SELECT     dateof
                            FROM          posts
                            WHERE      pid = MAX(dbo.posts.pid)) AS
dateof, GETDATE() AS timenow, dbo.tposts.fid,
                          (SELECT     COUNT(pid) - 1
                            FROM          posts
                            WHERE      tid = tposts.tid) AS replies,
dbo.tposts.tid, dbo.tposts.views, dbo.users.uname,
                          (SELECT     users.uname
                            FROM          users INNER JOIN
                                                   posts ON users.uid =
posts.uid
                            WHERE      (posts.pid =
MAX(dbo.posts.pid))) AS poster
FROM         dbo.posts INNER JOIN
                      dbo.tposts ON dbo.posts.tid = dbo.tposts.tid
INNER JOIN
                      dbo.users ON dbo.tposts.uid = dbo.users.uid
GROUP BY dbo.tposts.thread, dbo.tposts.fid, dbo.tposts.tid,
dbo.tposts.views, dbo.users.uname
ORDER BY
                          (SELECT     dateof
                            FROM          posts
                            WHERE      pid = MAX(dbo.posts.pid)) DESC

19 May 2006 6:38 PM

David Portas

nathan001 wrote:
Show quote

> Strategy should I use the following to select the last posting in my
> forum group
> in order to get the last poster I use the function that follows or
> should I use the select at end that subqueries everything?.
> view=
> SELECT     TOP 100 PERCENT dbo.tposts.thread, MAX(dbo.posts.pid) AS
> lastp, dbo.forums_S_lastby(MAX(dbo.posts.pid)) AS poster,
>                           (SELECT     dateof
>                             FROM          posts
>                             WHERE      pid = MAX(dbo.posts.pid)) AS
> dateof, GETDATE() AS timenow, dbo.tposts.fid,
>                           (SELECT     COUNT(pid) - 1
>                             FROM          posts
>                             WHERE      tid = tposts.tid) AS replies,
> dbo.tposts.tid, dbo.tposts.views, dbo.users.uname
> FROM         dbo.posts INNER JOIN
>                       dbo.tposts ON dbo.posts.tid = dbo.tposts.tid
> INNER JOIN
>                       dbo.users ON dbo.tposts.uid = dbo.users.uid
> GROUP BY dbo.tposts.thread, dbo.tposts.fid, dbo.tposts.tid,
> dbo.tposts.views, dbo.users.uname
> ORDER BY
>                           (SELECT     dateof
>                             FROM          posts
>                             WHERE      pid = MAX(dbo.posts.pid)) DESC
>
> where dbo.forums_S_lastby
>
> CREATE FUNCTION dbo.forums_S_lastby
>     (
>         @pid int
>     )
> RETURNS varchar(50)
> AS
>     BEGIN
>         declare @lastby varchar(50)
>         set @lastby='-'
>         if @pid is not null begin
>         SELECT @lastby=     users.uname
>         FROM         users INNER JOIN
>                               posts ON users.uid = posts.uid
>         WHERE     (posts.pid = @pid) end
>         /* sql statement ... */
>     RETURN @lastby
>     END
>
>
>
> view=
> SELECT     TOP 100 PERCENT dbo.tposts.thread, MAX(dbo.posts.pid) AS
> lastp,
>                           (SELECT     dateof
>                             FROM          posts
>                             WHERE      pid = MAX(dbo.posts.pid)) AS
> dateof, GETDATE() AS timenow, dbo.tposts.fid,
>                           (SELECT     COUNT(pid) - 1
>                             FROM          posts
>                             WHERE      tid = tposts.tid) AS replies,
> dbo.tposts.tid, dbo.tposts.views, dbo.users.uname,
>                           (SELECT     users.uname
>                             FROM          users INNER JOIN
>                                                    posts ON users.uid =
> posts.uid
>                             WHERE      (posts.pid =
> MAX(dbo.posts.pid))) AS poster
> FROM         dbo.posts INNER JOIN
>                       dbo.tposts ON dbo.posts.tid = dbo.tposts.tid
> INNER JOIN
>                       dbo.users ON dbo.tposts.uid = dbo.users.uid
> GROUP BY dbo.tposts.thread, dbo.tposts.fid, dbo.tposts.tid,
> dbo.tposts.views, dbo.users.uname
> ORDER BY
>                           (SELECT     dateof
>                             FROM          posts
>                             WHERE      pid = MAX(dbo.posts.pid)) DESC

DO NOT use "TOP 100 PERCENT" in views. Views are unordered and TOP 100
PERCENT is redundant and silly.

In general you can use a correlated subquery to return the latest row
of a set:

....
WHERE posting_date =
(SELECT MAX(posting_date)
FROM dbo.posts
WHERE ...)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

19 May 2006 7:35 PM

Jim Underwood

Try this approach. I think it is logically equivilant to what you had (yu
will need to verify), but without the large group by and without the top 100
percent. I moved most of the inline SQL to the from clause, either by joins
or by a new agregate.

When you access the view, you will want to add an order by to insure your
results are in the correct order. The order by within the view is not
dependable, so don't use it.

Also, while editing the SQL I changed the order of the select list, so you
may need to change it back if it is important to you.

SELECT   TPOSTS.THREAD,
         POSTS.LASTP,
         TPOSTS.FID,
         TPOSTS.TID,
         TPOSTS.VIEWS,
         USERS.UNAME,
         USERS2.UNAME AS POSTER,
         POSTS.REPLIES,
         MAXPOST.DATEOF
FROM     (select TID
               , max(PID)as LASTP
               , count(PID)-1 as REPLIES
               from POSTS
               group by TID) as POSTS
         INNER JOIN TPOSTS as TPOSTS
           ON POSTS.TID = TPOSTS.TID
         INNER JOIN USERS as USERS
           ON TPOSTS.UID = USERS.UID
         INNER JOIN USERS as USERS2
           ON USERS2.UID = POSTS.UID
           AND USERS2.PID = POSTS.LASTP
         INNER JOIN POSTS as MAXPOST
           ON MAXPOST.TID = POSTS.TID

Show quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1148063935.154204.6190@j55g2000cwa.googlegroups.com...
> nathan001 wrote:
> > Strategy should I use the following to select the last posting in my
> > forum group
> > in order to get the last poster I use the function that follows or
> > should I use the select at end that subqueries everything?.
> > view=
> > SELECT     TOP 100 PERCENT dbo.tposts.thread, MAX(dbo.posts.pid) AS
> > lastp, dbo.forums_S_lastby(MAX(dbo.posts.pid)) AS poster,
> >                           (SELECT     dateof
> >                             FROM          posts
> >                             WHERE      pid = MAX(dbo.posts.pid)) AS
> > dateof, GETDATE() AS timenow, dbo.tposts.fid,
> >                           (SELECT     COUNT(pid) - 1
> >                             FROM          posts
> >                             WHERE      tid = tposts.tid) AS replies,
> > dbo.tposts.tid, dbo.tposts.views, dbo.users.uname
> > FROM         dbo.posts INNER JOIN
> >                       dbo.tposts ON dbo.posts.tid = dbo.tposts.tid
> > INNER JOIN
> >                       dbo.users ON dbo.tposts.uid = dbo.users.uid
> > GROUP BY dbo.tposts.thread, dbo.tposts.fid, dbo.tposts.tid,
> > dbo.tposts.views, dbo.users.uname
> > ORDER BY
> >                           (SELECT     dateof
> >                             FROM          posts
> >                             WHERE      pid = MAX(dbo.posts.pid)) DESC
> >
> > where dbo.forums_S_lastby
> >
> > CREATE FUNCTION dbo.forums_S_lastby
> > (
> > @pid int
> > )
> > RETURNS varchar(50)
> > AS
> > BEGIN
> > declare @lastby varchar(50)
> > set @lastby='-'
> > if @pid is not null begin
> > SELECT @lastby=     users.uname
> > FROM         users INNER JOIN
> >                       posts ON users.uid = posts.uid
> > WHERE     (posts.pid = @pid) end
> > /* sql statement ... */
> > RETURN @lastby
> > END
> >
> >
> >
> > view=
> > SELECT     TOP 100 PERCENT dbo.tposts.thread, MAX(dbo.posts.pid) AS
> > lastp,
> >                           (SELECT     dateof
> >                             FROM          posts
> >                             WHERE      pid = MAX(dbo.posts.pid)) AS
> > dateof, GETDATE() AS timenow, dbo.tposts.fid,
> >                           (SELECT     COUNT(pid) - 1
> >                             FROM          posts
> >                             WHERE      tid = tposts.tid) AS replies,
> > dbo.tposts.tid, dbo.tposts.views, dbo.users.uname,
> >                           (SELECT     users.uname
> >                             FROM          users INNER JOIN
> >                                                    posts ON users.uid =
> > posts.uid
> >                             WHERE      (posts.pid =
> > MAX(dbo.posts.pid))) AS poster
> > FROM         dbo.posts INNER JOIN
> >                       dbo.tposts ON dbo.posts.tid = dbo.tposts.tid
> > INNER JOIN
> >                       dbo.users ON dbo.tposts.uid = dbo.users.uid
> > GROUP BY dbo.tposts.thread, dbo.tposts.fid, dbo.tposts.tid,
> > dbo.tposts.views, dbo.users.uname
> > ORDER BY
> >                           (SELECT     dateof
> >                             FROM          posts
> >                             WHERE      pid = MAX(dbo.posts.pid)) DESC
>
>
> DO NOT use "TOP 100 PERCENT" in views. Views are unordered and TOP 100
> PERCENT is redundant and silly.
>
> In general you can use a correlated subquery to return the latest row
> of a set:
>
> ...
> WHERE posting_date =
> (SELECT MAX(posting_date)
>   FROM dbo.posts
>   WHERE ...)
>
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

19 May 2006 9:25 PM

nathan001

would this be faster than the select below it

SELECT     TOP 100 PERCENT TPOSTS.thread, POSTS.LASTP, TPOSTS.fid,
TPOSTS.tid, TPOSTS.views, USERS.uname, POSTS.REPLIES, posts_1.dateof,
                      users_1.uname AS Expr1
FROM         (SELECT     TID, MAX(PID) AS LASTP, COUNT(PID) - 1 AS
REPLIES
                       FROM          POSTS
                       GROUP BY TID) POSTS INNER JOIN
                      dbo.tposts TPOSTS ON POSTS.TID = TPOSTS.tid INNER
JOIN
                      dbo.users USERS ON TPOSTS.uid = USERS.uid INNER
JOIN
                      dbo.posts posts_1 ON POSTS.LASTP = posts_1.pid
INNER JOIN
                      dbo.users users_1 ON posts_1.uid = users_1.uid
ORDER BY posts_1.dateof DESC

SELECT dbo.tposts.thread, MAX(dbo.posts.pid) AS lastp,
                          (SELECT     dateof
                            FROM          posts
                            WHERE      pid = MAX(dbo.posts.pid)) AS
dateof, GETDATE() AS timenow, dbo.tposts.fid,
                          (SELECT     COUNT(pid) - 1
                            FROM          posts
                            WHERE      tid = tposts.tid) AS replies,
dbo.tposts.tid, dbo.tposts.views, dbo.users.uname,
                          (SELECT     users.uname
                            FROM          users INNER JOIN
                                                   posts ON users.uid =
posts.uid
                            WHERE      (posts.pid =
MAX(dbo.posts.pid))) AS poster
FROM         dbo.posts INNER JOIN
                      dbo.tposts ON dbo.posts.tid = dbo.tposts.tid
INNER JOIN
                      dbo.users ON dbo.tposts.uid = dbo.users.uid
GROUP BY dbo.tposts.thread, dbo.tposts.fid, dbo.tposts.tid,
dbo.tposts.views, dbo.users.uname
ORDER BY
                          (SELECT     dateof
                            FROM          posts
                            WHERE      pid = MAX(dbo.posts.pid)) DESC

19 May 2006 10:06 PM

David Portas

nathan001 wrote:

> would this be faster than the select below it
>

Did you try it? Without access to your data and hardware any reply you
get is going to be a guess at best.

You can remove the subquery in your final ORDER BY. "ORDER BY dateof"
should be equivalent even though it may not be what you intended. Plus
you still didn't remove TOP 100 PERCENT. That bit of code is not doing
you any favours.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

19 May 2006 8:55 PM

nathan001

ultimately I will not have this in a view so I would not have the top
100 percent in the select thats just what the sql builder put in there.
Thanks for the infomation.