CODEWITHSUNDEEP
mysqlselect
Tudor
Tudor

Reputation: 1181

How to select distinct rows that match multiple elements from same column in 1 query?

ID        object        type
1           1            u            
2           1            x
3           1            z
4           2            z
5           2            t
6           4            x
7           4            u
8           4            t

Asuming i have that set of data , what i want to get is all distinct objects that would match some variable number of types, and order them by the number of types that matched. All in 1 query.

So lets say i want the objects that are of type u,x,t . I want to select them in descendent order from the object that matched all to the one that matched only 1 in 1 query. If any1 can help, im using mysql ...

Upvotes: 1

Views: 975

Answers (2)

redmoon7777
redmoon7777

Reputation: 4526

here you go:

SELECT *, COUNT(ID) AS matches FROM table_name WHERE type IN('u','x','t') GROUP BY object ORDER BY matches DESC

Upvotes: 3

Rufinus
Rufinus

Reputation: 30721

Not sure i understand what you trying todo but i guess it will lead to:

SELECT * FROM table WHERE type IN ('u','x','t') GROUP By object ORDER By object;

Upvotes: 0

Related Questions