On Thu, 2 Mar 2006 16:15:36 -0700, "Andrew" <AndrewR***@hotmail.com>
wrote:

>Hey all,
>
>Having trouble forming a SQL Query, could use the help of one of you Gurus.
>
>The table structure:
>TABLE [tbl_HCA_Inter_User_Status] (
> [intStatusID] [int] IDENTITY,
> [intUser_Info_ID] [int],
> [intStatus] [int],
> [txtNote] [varchar] (4000),
> [txtUpda_By] [varchar] (50),
> [dtmUpda_Date] [datetime],
> [txtCrea_By] [varchar] (50),
> [dtmCrea_Date] [datetime]
>)
>
>This table tracks the current status of users.  Status can be 1 - 9.
>However, the records are not updated, rather the table is more of a running
>history....that is, each time a user's status changes a new record is added
>so we can see when it changed, what it was prior, and so forth.
>
>I could use some help creating a query that would pull back from the table,
>a list of users, giving their most recent status.  Another way of putting
>it -- for each distinct user in the table return the record of their most
>recent status entry.  And as one single result set that is.
>
>I was able to get one to work for a specific user (below), but can't seem to
>puzzle out how to do it for all users in the table.
>
>SELECT TOP 1 *
>FROM tbl_HCA_Inter_User_Status
>WHERE intUser_Info_ID = @UserID
>ORDER BY intStatusID DESC
>
>Any help is greatly appreciated!  Thanks!
>
>-- Andrew
>

"Roy Harvey" <roy_har***@snet.net> wrote in message
news:su0f02516mtnotofu721hd55hl808uob1h@4ax.com...
> Here are a few alternatives.
>
> SELECT *
>  FROM tbl_HCA_Inter_User_Status as T1
> WHERE NOT EXISTS
>       (select * from tbl_HCA_Inter_User_Status as T2
>         where T1.intUser_Info_ID = T2.intUser_Info_ID
>           and T1.intStatusID < T2.intStatusID)
>
> SELECT *
>  FROM tbl_HCA_Inter_User_Status as T1
> WHERE intStatusID =
>       (select max(T2.intStatusID)
>          from tbl_HCA_Inter_User_Status as T2
>         where T1.intUser_Info_ID = T2.intUser_Info_ID)
>
> SELECT T1.*
>  FROM tbl_HCA_Inter_User_Status as T1
>  JOIN (select intUser_Info_ID,
>               max(intStatusID) as intStatusID
>          from tbl_HCA_Inter_User_Status) as T2
>    ON T1.intUser_Info_ID = T2.intUser_Info_ID
>   AND T1.intStatusID = T2.intStatusID
>
> Roy Harvey
> Beacon Falls, CT
>
>
> On Thu, 2 Mar 2006 16:15:36 -0700, "Andrew" <AndrewR***@hotmail.com>
> wrote:
>
>>Hey all,
>>
>>Having trouble forming a SQL Query, could use the help of one of you
>>Gurus.
>>
>>The table structure:
>>TABLE [tbl_HCA_Inter_User_Status] (
>> [intStatusID] [int] IDENTITY,
>> [intUser_Info_ID] [int],
>> [intStatus] [int],
>> [txtNote] [varchar] (4000),
>> [txtUpda_By] [varchar] (50),
>> [dtmUpda_Date] [datetime],
>> [txtCrea_By] [varchar] (50),
>> [dtmCrea_Date] [datetime]
>>)
>>
>>This table tracks the current status of users.  Status can be 1 - 9.
>>However, the records are not updated, rather the table is more of a
>>running
>>history....that is, each time a user's status changes a new record is
>>added
>>so we can see when it changed, what it was prior, and so forth.
>>
>>I could use some help creating a query that would pull back from the
>>table,
>>a list of users, giving their most recent status.  Another way of putting
>>it -- for each distinct user in the table return the record of their most
>>recent status entry.  And as one single result set that is.
>>
>>I was able to get one to work for a specific user (below), but can't seem
>>to
>>puzzle out how to do it for all users in the table.
>>
>>SELECT TOP 1 *
>>FROM tbl_HCA_Inter_User_Status
>>WHERE intUser_Info_ID = @UserID
>>ORDER BY intStatusID DESC
>>
>>Any help is greatly appreciated!  Thanks!
>>
>>-- Andrew
>>