Select Statement

[vellaima]

UserID GrpID Rights

 

U001 A V

U001 B M

U002 A V

U003 A V

U003 B M

 

There are two groups A and B. I want to select all the user_id's but if there are any duplicates, i want to select the user_id which has Rights "M"

 

In simple words, can anyone please tell me a select statement that displays,

 

UserID Rights

U0001 M

U0002 V

U0003 M

 

Please do help me out.

[quwiltw]

Maybe not the most efficient way, but it should get you going for now...

SELECT Distinct(T1.UserID),  (SELECT Min(T2.Rights)  From Table1 T2 WHERE T2.UserID = T1.UserID) As Rights From Table1 T1 

[Robby]

quwiltw from what I gather, there's only one table.

[quwiltw]

quwiltw from what I gather, there's only one table.

T2 is just an alias, that statement is only based on one *real* table.

[Robby]

Idiot me, I should've look closer.

[vellaima]

quwiltw, thanks for replying. Instead of using select statement, can we use store it in a dataset and then delete duplicate rows where Rights="V". Is it possible.

[Raju]

hi vidya,

Try this query ( as per the sample data you given),let me know..

 

SELECT USERID, RIGHTS

FROM [uSER]

WHERE RIGHTS = 'M' OR USERID NOT IN (SELECT USERID FROM USER WHERE RIGHTS <> 'M') AND

RIGHTS = 'V' OR USERID NOT IN (SELECT USERID FROM USER WHERE RIGHTS <> 'V');

 

 

Raju

[vellaima]

Thanks for the select statement. It is working fine.

[vellaima]

quwiltw,

 

Can please clarify whether "max" means it displays last row added and "min" means it displays the very first row added as i am not using the primary key here.

[quwiltw]

Since you wanted the 'M' 's before the 'V's I did min because M < V . It has nothing to do with when they were added. I guess it was just a quick way around seleting the Top 1 ordered by Rights desc. Get it? Here's a better explanation (archived, but accurate as far as this discussion):

http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/accessdev/html/ODC_SQLMinAndMax.asp

[vellaima]

Thanks Quwiltw for clarification.

Since the rights is either M or V, can I use Max and Min in my SQL Statement.

[quwiltw]

Yes. That's what I have above. Maybe I'm misunderstanding your question?

[vellaima]

quwiltw, just to be sure i am asking such question. Please do bear with it. Thanks for providing that SQL Statement. It is working perfectly well.