0

I have MySQL statement where I'm trying to select distinct rows with the latest date.

This is the SQL statement:

SELECT st.seno, tc.pl, tc.sno, st.val1, st.val2, st.date 
FROM tc
LEFT JOIN st ON tc.seno = st.seno AND tc.pl = st.pl AND st.seno = 1304239136
WHERE tc.pl = 1
ORDER BY st.date DESC

This is the data returned: enter image description here

I want to distinct by unique 'tc.sno', therefore I only want the first and third row returned as the middle date is earlier then the top one for the 'sno' 3. The 'sno' could always be different so I do not want to hardcode those numbers. I was trying to use 'GROUP BY' but it just picks the first value of the date, tried using 'HAVING' and combining select statements together but can't seem to get it working. (the val1 and val2 in row 2 could be random it is just a coincidence that they exact in this example)

  • 1
    the second row: 15:34:20 is first value of the date. Why do you want to see row 1 and 3 in the output, not 2 and 3 in this case? – Eray Balkanli Apr 04 '18 at 16:03
  • 1
    Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – Paul Spiegel Apr 04 '18 at 16:05
  • Is there a unique ID column in the table? Otherwise, you would have to determine which columns provide a unique way to select a particular row. sno is not unique, so it can't be used to select a particular row, but you can group by it. – Sloan Thrasher Apr 04 '18 at 16:05
  • @ErayBalkanli I do not want the first value I want the earliest date from today.Maybe you are right it is slightly confusing. This is to trace what records were previously inserted. –  Apr 04 '18 at 16:52
  • @SloanThrasher There is no unique ID and there cannot be one as some rows might return null values for specific 'seno'. If I group by it the date is not in the order I require. –  Apr 04 '18 at 16:54
  • There *could* be a unique column if you added an auto-increment integer column. The reason I bring this up is then you could get the specific row for each sno with the max date and join back to the table to get the result you require, iow, with the max date for each sno. – Sloan Thrasher Apr 04 '18 at 21:51

0 Answers0