How can I make data that looks like this:

Job_Id  Monitor_Date
B119    3/11/2010
B119    3/07/2013
B119    15/05/2020
B121    03/05/2015
B167    3/12/2010
B167    3/07/2013
B167    15/10/2015
B189    31/07/2015
B189    07/05/2015

Look like this - so that only 1 record for each Job_ID and it is the most recent Monitor_Date:

Job_Id  Monitor_Date
B119    15/05/2020
B121    13/05/2015
B167    15/10/2015
B189    7/05/2020
  • 1
    Specify your DBMS precisely, including its version. – Akina Jun 18 at 7:44
  • 1
    and if possible no pictures - use text so helpers can easily copy your sample data to sqlfiddle or test programs .... – eagle275 Jun 18 at 9:04
  • Are you storing your "dates" as strings? It looks like it because in your sample data you have "dates" where the first component have both leading zeros and without. – Colin 't Hart Jun 19 at 9:49
SELECT Job_Id, MAX(Monitor_Date) Monitor_Date
FROM source_table
| improve this answer | |
  • Thanks - this is exactly what I needed – cate Jun 22 at 0:23

You can use the order by to define the returned order from the select. MS SQL you can use 'top 4' and Oracle I think it's rowcount to limit the returned rows. The SQL engine would have to understand the monitor date column so if its a text field you would have to convert it to date during the query using a function like todate(3/11/10, xxx).

| improve this answer | |
  • 2
    anybody using varchar or similar to store date / datetime deserves to be nailed onto a cross – eagle275 Jun 18 at 9:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.