SQL - how to efficiently select distinct records

I've got a very performance sensitive SQL Server DB. I need to make an efficient select on the following problem:

I've got a simple table with 4 fields:

ID [int, PK]
UserID [int, FK]
Active [bit]
GroupID [int, FK]

Each UserID can appear several times with a GroupID (and in several groupIDs) with Active='false' but only once with Active='true'.

Such as:

(id,userid,active,groupid)
1,2,false,10
2,2,false,10
3,2,false,10
4,2,true,10

I need to select all the distinct users from the table in a certain group, where it should hold the last active state of the user. If the user has an active state - it shouldn't return an inactive state of the user, if it has been开发者_如何学编程 such at some point in time.

The naive solution would be a double select - one to select all the active users and then one to select all the inactive users which don't appear in the first select statement (because each user could have had an inactive state at some point in time). But this would run the first select (with the active users) twice - which is very unwanted.

Is there any smart way to make only one select to get the needed query? Ideas?

Many thanks in advance!

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 ....

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.

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:

1. 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.

2. 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