CODEWITHSUNDEEP
phpmysqlsqlgroup-bydistinct
Brad
Brad

Reputation: 12262

Return results based on distinct on certain column in one situation

I want to return the most recent (dateCreated desc) of each record in two different situations.

This might not even be possible, but thought I'd reach out on here.

1) if the record has a value in the parentId column, then I want to search thru all the records with that same parentId and include only the one that is the most recent (dateCreated desc) in the results.

I know how to do that w/ a query like this:

select distinct(parentId) from table order by dateCreated desc

But I don't know how to accomplish that while trying to account for both situations.

2) if the record does not contain a parentId and that record's ID is not in another record's parentId, then include that in the result.

I know I could get all the results, then programmatically loop thru it like so:

for($i<0; $i<count($results); $i++)
{
   if ($results[$i]['parentId'])
   {
      $parentId = $results[$i]['parentId'];
      $parentDateCreated = $results[$i]['dateCreated'];

      for($k=0; $k<count($results); $k++)
      {
         if($results[$k]['parentId'] = $parentId)
         {
            if ( $results[$i]['dateCreated'] < $results[$k]['dateCreated'] )
            {
                $data[$parentId] = $results[$i];
            }
         }
      }
   }
}

Probably not efficient and not sure if it works, but wanted to give an idea of what I need to accomplish, I need to find the most recent records in the case that it has a value in the parentId (which it needs to be compared with all other records with the same parentId) or if the record does not contain a parentId, then just include that in the result as well.

I created sql fiddle with a basic query. http://sqlfiddle.com/#!9/69544/1

Sample data:

id  parentId  dateCreated
--  --------  -------------------
1   null      2016-04-10 20:00:00
2   1         2016-04-12 12:00:00
3   1         2016-04-13 10:00:00
4   null      2016-04-15 14:33:00

Expected results (I'll just return the ids I expect in the result):

3 and 4.

id 3, because it found a parentId of 1 and found the most recent one in id 3 because of the dateCreated was more recent.

id 4, because it did not have a parentId and nor did 4 show up in any other record's parentId column.

Upvotes: 0

Views: 36

Answers (1)

Barmar
Barmar

Reputation: 781380

Use a UNION of two queries. One query gets the most dateCreated for each parentID where that field is not null. The other query returns all the records where parentID is null.

SELECT t1.*
FROM yourTable AS t1
JOIN (SELECT parentId, MAX(dateCreated) AS maxCreate
      FROM yourTable
      WHERE parentID IS NOT NULL
      GROUP BY parentID) AS t2
ON t1.parentID = t2.parentID AND t1.dateCreated = t2.maxCreate
UNION
SELECT *
FROM yourTable
WHERE parentID IS NULL
AND id NOT IN (
    select parentID 
    from yourTable
    WHERE parentId IS NOT NULL)

DEMO

Upvotes: 1

Related Questions