-3

sample data

How to select row for each Code with minimum date?

The result should be ..

  1. HS00001 2014-01-10
  2. HS00002 2014-03-10
  3. HS00003 2014-08-20

P.S. This is neither H.W. nor Assignment. I am just trying to create a template in wordpress using php and mysql database.

Bilal Akil
  • 4,716
  • 5
  • 32
  • 52
mistysnake
  • 113
  • 1
  • 2
  • 8

2 Answers2

0

After reading the revised question... I think this is what you want:

SELECT *
FROM House as H
WHERE date = (
    SELECT MIN(date)
    FROM House
    WHERE H.code = code
  )
GROUP BY code

The GROUP BY code makes sure that even if there are multiple entries with the same MIN(date) then only one will be selected. Which one is selected, however, is random unless you add more stuff into that subquery to filter it more.

Bilal Akil
  • 4,716
  • 5
  • 32
  • 52
0

This seems a lot like HW, so I won't give you the code, but it looks like all the houses are from Toronto, so you'll want to have a WHERE clause with Toronto somewhere in there, and then you will also want to have an ORDER BY date DESC clause

In your PHP, you'll probably want a mysqli_fetch_assoc() in there, and loop through that and display what you want in a table format

scrblnrd3
  • 7,228
  • 9
  • 33
  • 64