CODEWITHSUNDEEP
mysqlgroup-byunique
sanchitkhanna26
sanchitkhanna26

Reputation: 2243

Fetch the rows with unique value in a particular column - MySQL

I have the following mysql query -:

I have a table -> 

feed_id | by_id | sharer

It has following values -> 

 (1 , 1 , NULL)  , (2, 1, iop_23) , (3, 1, iop_23)

I run this query SELECT * FROM feed WHERE by_id = 1 GROUP BY sharer ORDER BY feed_id DESC LIMIT 10

With this query I am trying to fetch the rows which have either sharer = NULL or if not NULL then a Unique Value.

It should return -> (1 , 1 , NULL) , (3, 1, iop_23)

But it is returning -> (1 , 1 , NULL) , (2, 1, iop_23)

Upvotes: 0

Views: 158

Answers (3)

echo_Me
echo_Me

Reputation: 37243

try this

   SELECT max(feed_id) as feed_id ,by_id ,sharer FROM feed 
   WHERE by_id = 1 
   GROUP BY sharer 
   ORDER BY feed_id DESC LIMIT 10

DEMO HERE

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271141

The reason it is returning the wrong value is because you have columns in the select that are not in the group by and that have no aggregation functions. The results are indeterminate.

If you want the one with the biggest id:

select s.*
from sharer s left outer join
     (select sharer, max(feed_id) as maxf
      from feed
      group by sharer
     ) sf
     on s.feed_id = maxf
where s.sharer is null or sf.sharer is not null

You need the left outer join to get all the NULL values. The where clause is to select between the NULL values on the one hand and the max id rows on the other.

It looks like you want the row with the biggest id for each sharer plus all the rows with NULL values. Perhaps I misunderstand the question, but that is my interpretation.

This finds those rows by first finding the maximum id for each sharer, and then joining this back to the original data.

Upvotes: 1

landons
landons

Reputation: 9547

That's the correct result for a GROUP BY. To get the last record returned for the group, you'd need an inner query to sort first, then group in the outer query:

SELECT * FROM (
    SELECT * FROM feed WHERE by_id = 1 ORDER BY feed_id DESC
) AS `a` GROUP BY sharer LIMIT 10

Upvotes: 0

Related Questions