CODEWITHSUNDEEP
mysqlsql
jpganz18
jpganz18

Reputation: 5858

how can I get all the rows where a field value appears just one time?

Hope you can understand what I want to archive...

I have something like this

table: my_user

id - username - user_profile - user_status - user_key

so, in the case i have a content like this

1 - John Doe - profile1 - ok - key12345
2 - John Doe - profile2 - ok - key12345
3 - John Doe - profile2 - ok - key12345
4 - John Doe - profile2 - ok - key12346
5 - John Doe - profile2 - ok - key12347

I do have all the different keys like 

key12345
key12346
key12347

Is there any way in a query where I can query my table asking for all the results that matches have this key BUT retrieving just the first record that has it? like:

1 - John Doe - profile1 - ok - key12345
4 - John Doe - profile2 - ok - key12346
5 - John Doe - profile2 - ok - key12347

I tried with something like this

select * from my_user where user_key IN (key12345, key12346, key12347)

But I get all the rows, is it possible to do this?

Upvotes: 0

Views: 28

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use variable to emulate row_number()

SQL Demo

SELECT *
FROM (
       SELECT my_user.*,
              @rn := IF(@user = user_key,
                        @rn + 1,
                        IF(@user:= user_key, 1, 1)
                       ) as rn         
       FROM my_user 
       CROSS JOIN (SELECT @rn := 0, @user := 0) t
       where user_key IN ('key12345', 'key12346', 'key12347')
       ORDER BY user_key
     ) F
WHERE F.rn = 1
ORDER BY id;

OUTPUT

enter image description here

Upvotes: 2

Darshan Mehta
Darshan Mehta

Reputation: 30819

Can you try the following query:

SELECT * 
FROM my_user u 
WHERE ID IN (
 SELECT MIN(ID) 
 FROM my_user 
 WHERE user_key = u.user_key  
)

Here's the SQL Fiddle.

Upvotes: 2

Related Questions