0

I have this problem. I can't think a query for find some values which are repeating in my table, I want to select only those with the latest/highest Scan Date(marked with yellow) Here my table, name:Test enter image description here

Cœur
  • 37,241
  • 25
  • 195
  • 267
Bombo
  • 137
  • 1
  • 1
  • 10
  • What do you have so far? How can you have multiple latest scan dates for the same person? – David B May 31 '17 at 09:26
  • Sorry but this database wasn't created by me. i have multiple lastest scan dates from the same person, so i should create this query to select those Scan_Date – Bombo May 31 '17 at 09:48
  • This isn't an exact duplicate due to the way the data is ordered. The key to this question is handling the `SCAN_DATE` in the `GROUP BY` function which isn't covered in the two duplicate questions that are suggested – Tom May 31 '17 at 10:05

1 Answers1

1

You need to use the MAX function and GROUP BY. This should select the latest SCAN_DATE from your table

SELECT
    LAST_NAME
    ,FIRST_NAME
    ,MAX(SCAN_DATE)
    ,WEIGHT
    ,HEIGHT
FROM Test
-- If using SQL
GROUP BY LAST_NAME, FIRST_NAME, CAST(SCAN_DATE AS Date), WEIGHT, HEIGHT
-- If using MySQL
-- GROUP BY LAST_NAME, FIRST_NAME, DATE(SCAN_DATE), WEIGHT, HEIGHT
Tom
  • 9,725
  • 3
  • 31
  • 48
  • sorry, but with this query, i get the last row of my table, instead of the yellow-marked rows – Bombo May 31 '17 at 09:51
  • Can you have a look at the update. Also correct your tags to whether you're using `sql` or `mysql` as they have different syntaxes – Tom May 31 '17 at 09:56
  • it worked , thank you so much!!! – Bombo May 31 '17 at 10:02