2 Replies - 168 Views - Last Post: 18 September 2017 - 12:25 AM

#1 comosejama  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 25-September 16

select all rows with distinct and condition in MySQL

Posted 16 September 2017 - 11:16 AM

Hi guys I new in mySQL and I have problem with query. I was trying to write some Query which get me all records from table and if I have two records with the same date i need get only record between this two which have manual_selection = 1.

So I need get 8 records from my table all except id = 1401 and id = 1549
Posted Image

I tried to combine how can I get this records like this:

SELECT * FROM project.score WHERE project_id = 358 
AND crawled_at IN(SELECT crawled_at FROM project.score WHERE project_id = 358 
AND manual_selection = 1 GROUP BY crawled_at)
ORDER BY crawled_at;      

SELECT * FROM project.score WHERE project_id = 358 
GROUP BY crawled_at HAVING manual_selection = 1; 

but all my way always get only rows with manual_selection = 1. I havent idea how can I distinct rows with duplicate "crawled_at" on case where manual_selection = 1. Can someone help me?

Is This A Good Question/Topic? 0
  • +

Replies To: select all rows with distinct and condition in MySQL

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 5059
  • View blog
  • Posts: 13,654
  • Joined: 18-April 07

Re: select all rows with distinct and condition in MySQL

Posted 16 September 2017 - 06:06 PM

Well I think you could try something tricky like...

select * from projects where projects.id not in 
	(select id from projects as s,
		(select crawled_at from projects group by crawled_at having count(id) > 1) 
	as t where s.crawled_at = t.crawled_at and manual_selection = 0)



So the inner-most query gets us those dates which are duplicates. That is, having a count greater than 1. Using that we then select the ids for which crawled dates match and selection is zero. This essentially gives us the ids which we want to filter out of the list. In your case ids 1401 and 1549. Then from that we select all records which are not part of that list. So essentially everything that is not 1401 and 1549.

Now I am assuming here that manual_selection will be either 0 or 1 only and that your record number isn't going to be really big (bigger than perhaps 10's of thousands of records).
Was This Post Helpful? 0
  • +
  • -

#3 comosejama  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 25-September 16

Re: select all rows with distinct and condition in MySQL

Posted 18 September 2017 - 12:25 AM

Ok it work in clear MySQL but how can I used it in symfony?
$dql = "SELECT m FROM AppBundle:project m where m.projectId = :projectId AND m.id not in
    (SELECT s.id FROM AppBundle:project s,
        (SELECT q.crawledAt FROM AppBundle:project q WHERE q.projectId = :projectId GROUP BY q.crawledAt having count(q.id) > 1)
    as t where s.crawledAt = t.crawledAt and s.manualSelection = 0)";

		$query = $em->createQuery($dql);
		$query->setParameter('projectId', $projectId);


I have errorn near
(SELECT q.crawledAt


{"message":"[Semantical Error] line 0, col 164 near \u0027(SELECT q.crawledAt\u0027: Error: Class \u0027(\u0027 is not defined."}
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1