This should be simple, the but the solution is eluding me...

Sample Table/View:

user_id order_id status
1 1 open
1 2 open
1 3 closed
2 4 closed
2 5 closed


I need a query that will get me all the users that have order_IDs that have more than one of the same type of status (WITHOUT specifying what the statuses may be in advance). For example the results I need from this query look like:


order_id
1
2
4
5


Not sure how to do this. Conceptually, its:

select order_id
from table
group by user_id
having count(status)>1

Of course, thats not valid SQL - I'd have to include order_id in the GROUP BY clause, and since order IDs are unique that would give zero results. How do I solve this problem??

Something like:

select order_id
from table
where cast(user_id as varchar(20)) + status in
(select cast(user_id as varchar(20)) + status
from table
group by cast(user_id as varchar(20)) + status
having count(*)>1)

Performance will be crap though - not sure which version of sql you are using. Storing the combined userid and status as a column and indexing it would improve things dramatically if performance is an issue. If you are using MSSQL you could make this a computed column and set the persist property to true.

Interesting hack.. brilliant actually.

So you'd basically be comparing userid+status as a string versus the subselected userid+status.. that allows you to pull orderID without needing a group by in the main query. Brilliant.

Performance not an issue - only about 600 rows to analyze. THANKS.