SQL - how to efficiently select distinct records?

What about a view such as this : createview ACTIVE as select * from USERS where Active = TRUE Then just one select from that view will be sufficient : select user from ACTIVE where ID ....

That's not so good - because there are hundred thousands of records in that table - I wouldn't like to get all of the active ones... – roman Oct 2 at 14:19 Indeed, the CREATE VIEW will take a few moments to be done, but that runs only once! – Louis Oct 2 at 14:34 2 Unless you're creating a materialized view, the view does not run until you read data from it, and then it runs every time you read data from it. – Larry Lustig Oct 2 at 14:46 Indeed, thnks for the precision.

– Louis Oct 2 at 14:54.

Try this: Select ug. GroupId, ug. UserId, max(ug.

Active) LastState from UserGroup ug group by ug. GroupId, ug. UserId If the active field is set to 1 for a user / group combination you will get the 1, if not you will get a 0 for the last state.

That's a very interesting idea.... :) Though it seems that the MAX function couldn't be used on a bit field – roman Oct 2 at 14:58 But if the true record is not the "most recent", you'll still get true even though the user is no longer active in that group. – Larry Lustig Oct 2 at 15:20.

I'm not a big fan of the use of an "isActive" column the way you're doing it. This requires two UPDATEs to change an active status and has the effect of storing the information about the active status several times in the different records. Instead, I would remove the active field and do one of the following two things: If you already have a table somewhere in which (userid, groupid) is (or could be) a PRIMARY KEY or UNIQUE INDEX then add the active column to that table.

When a user becomes active or inactive with respect to a particular group, update only that single record with true or false. If such a table does not already exist then create one with '(userid, groupid)as thePRIMARY KEYand the fieldactive` and then treat the table as above. In either case, you only need to query this table (without aggregation) to determine the users' status with respect to the particular group.

Equally importantly, you only store the true or false value one time and only need to UPDATE a single value to change the status. Finally, this tables acts as the place in which you can store other information specific to that user's membership in that group that applies only once per membership, not once per change-in-status.

Try this: SELECT t. * FROM tbl t INNER JOIN ( SELECT MAX(id) id FROM tbl GROUP BY userid ) m ON t. Id = m.id.

Not sure that I understand what you want your query to return but anyway. This query will give you the users in a group that is active in the last entry. It uses row_number() so you need at least SQL Server 2005.

Table definition: create table YourTable ( ID int identity primary key, UserID int, Active bit, GroupID int ) Index to support the query: create index IX_YourTable_GroupID on YourTable(GroupID) include(UserID, Active) Sample data: insert into YourTable values (1, 0, 10), (1, 0, 10), (1, 0, 10), (1, 1, 10), (2, 0, 10), (2, 1, 10), (2, 0, 10), (3, 1, 10) Query: declare @GroupID int = 10 ;with C as ( select UserID, Active, row_number() over(partition by UserID order by ID desc) as rn from YourTable as T where T. GroupID = @GroupID ) select UserID from C where rn = 1 and Active = 1 Result: UserID ----------- 1 3.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.