Query help - get last username?

Hot Topic

Currency conversion wizard - performance
WSDL Support in WebService Task
Secruity Question, putting SQL Server 2000 or 2005 on Windows Server 2003 running a website
problem with custom conflict resolution
Missing SqlDataReader.GetSqlMetaData() in April CTP
USING the IN in STORED PROCEDURE
Problem With Stored Procedure
Round Function showing nothing in results when zero
Connection to Oracle
multiple-step ole db error

SQL Server

Show multiple order records in single row
how to insert more than once when processing one row in script component
Do you regularly reboot your server?
Moving or rolling average, how to?
my problem in detail
copying table in a database
Cluster installation & Domain Group requirements...
Strange cube processing error
.NET Remoting or Direct Connection to SQL Server 2005 Express
Setup RS for deployment and running reports by users

Query help - get last username?

Hi, I hope this is the right place to post this. I am pretty much stuck but think I am doing pretty good so far as I am getting more and more into SQL

using SQL Server 2000 here.

 

I want to be able to get the last username who replied to a topic. The "DateOfLastReply" works great, it gives me the last date of a reply to a topic so pretty much along with this, I want to get who it was that replied last. Any ideas so far, I have this but it isnt correct:




SELECT Threads.[ID] 'ThreadID', ThreadName, DateOfCreation 'DateCreated',

(SELECT TOP 1 DateOfReply FROM Replies WHERE Replies.ThreadID = Threads.[ID] ORDER BY DateOfReply DESC) 'LastReplyDate',

(SELECT TOP 1 e.Username FROM Replies, Threads WHERE Replies.UserID = e.[ID] AND Threads.[ID] = Replies.ThreadID AND Threads.ThreadStarterUser = e.[ID] ORDER BY DateOfReply DESC) 'LastUserReplied', --HERE

(SELECT COUNT(ReplyID) FROM Replies WHERE ThreadID = Threads.[ID]) 'NumberOfReplies',
e.username 'UsernameCreator'
FROM Threads
INNER JOIN Users e ON
e.[ID] = ThreadStarterUser

 




Answer this question

Query help - get last username?

  • Swapnil Mahankal

    You can simplify it a bit better using the APPLY operator in SQL Server 2005 and the COUNT aggregate using OVER clause. Also, please don't use the 'alias' method to specify alias. Use the one with AS keyword and quoted or bracketed identifiers. Ex:
    SELECT Threads.[ID] AS "ThreadID" /* or [ThreadID] */,
    ThreadName,
    DateOfCreation AS "DateCreated",
    r1.DateOfReply AS "LastReplyDate",
    a.Username     AS "LastUserReplied",
    r1."NumberOfReplies",
    e.username AS "UsernameCreator"
    FROM Threads
    INNER JOIN Users e ON
    e.[ID] = ThreadStarterUser
    CROSS APPLY (
    SELECT TOP 1 r.DateOfReply, r.UserID, COUNT(*) OVER() AS "NumberOfReplies"
    FROM Replies AS r
    WHERE r.ThreadID = Threads.[ID]
    ) as r1
    JOIN Users a
    ON a.[ID] = r1.UserID


  • David Lowe

    This seems to work, but I am wondering if perhaps there is a better way



    SELECT Threads.[ID] 'ThreadID', ThreadName, DateOfCreation 'DateCreated',

    (SELECT TOP 1 DateOfReply FROM Replies WHERE Replies.ThreadID = Threads.[ID] ORDER BY DateOfReply DESC) 'LastReplyDate',
    (SELECT TOP 1 a.Username FROM Replies
    INNER JOIN Users a ON
    a.[ID] = Replies.UserID
    WHERE Replies.ThreadID = Threads.[ID] AND a.[ID] = Replies.UserID
    ORDER BY DateOfReply DESC) 'LastUserReplied',

    (SELECT COUNT(ReplyID) FROM Replies WHERE ThreadID = Threads.[ID]) 'NumberOfReplies',
    e.username 'UsernameCreator'
    FROM Threads
    INNER JOIN Users e ON
    e.[ID] = ThreadStarterUser



  • Peter N Roth

    There is no other easy way to do the same in SQL Server 2000 unfortunately. The column alias syntax that is ANSI SQL standard specific are following:
    column_expr [AS] column_alias
    column_expr [AS] "column_alias"
    column_expr [AS] [column_alias]
    The following are SQL Server specific:
    column_expr [AS] 'column_alias'
    column_alias = column_expr
    'column_alias' = column_expr *** This is deprecated in SQL Server 2005 ***
    It is preferable to use the ANSI SQL syntax as far as possible. You can omit the " or bracket delimiters if the alias doesn't contain special characters. Single-quotes are used as delimiters for strings also so it will be confusing for someone new if you use the 'column_alias' syntax. The SQL Server specific syntaxes might also be deprecated in a future version.


  • jeffps

    Thank-you for your valuable help! Much appreciated. I am actually using SQL Server 2000 but hopefully the query you have given should also work...

    Edit: doesnt work for SQL Server 2000 - Unfortunatly the hosting Server only has SQL Server 2000 :(

     

    Could I ask - why should we be using the AS keyword with 'alias' What is the difference between doing that, and just giving it the alias for a field name - I am just curious for my knowledge



  • Mr_Gear

    Thats great, thank-you for your valuable help and advice!

    Have a great day



  • Query help - get last username?
Answer this question