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_ids 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, theres only one table.

 

[quwiltw]

Originally posted by Robby
quwiltw from what I gather, theres only one table.

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

 

[Robby]

Idiot me, I shouldve 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 Vs 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? Heres 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. Thats what I have above. Maybe Im 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.