CODEWITHSUNDEEP
phpsql
Calibur Victorious
Calibur Victorious

Reputation: 638

Selecting only one row from duplicated values column

[ title   -    date  ]
[  PHP    -  10/10/10]
[  PHP    -  10/10/11]
[  PDO    -  10/10/12]
[  ASP    -  10/10/13]
[  ASP    -  10/10/16]
[  PHP    -  10/10/18]

From this table when i use this query

$query = "SELECT DISTINCT title, date FROM posts WHERE title = :title ORDER BY title, date";

and go

Prepare
Execute
while($row = $stmt->fetch()) {
  $title = $row['title'];
  $date = $row['date'];

  echo "<div>$title</div><div>$date</div>";
 }

i still get all the values and not only one value with the title PHP, I only want to pick the value with the highest date

i want it to be

<div>ASP</div><div>10/10/16</div>
<div>PDO</div><div>10/10/12</div>
<div>PHP</div><div>10/10/18</div>

Upvotes: 1

Views: 44

Answers (2)

dnoeth
dnoeth

Reputation: 60513

Looks like a simple aggregation:

SELECT title, MAX(date) 
FROM posts 
GROUP BY title
ORDER BY title

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If you want only one row, use limit 1:

SELECT title, date
FROM posts
WHERE title = :title
ORDER BY date DESC
LIMIT 1;

Upvotes: 0

Related Questions