SQL SELECT most recently created row WHERE something is true

Question

I am trying to SELECT the most recently created row, WHERE the ID field in the row is a certain number, so I don't want the most recently created row in the WHOLE table, but the most recently created one WHERE the ID field is a specific number.

My Table:

Table:

   |   name  |   value    |   num |SecondName|    Date      |  
   | James   |   HEX124   |   1   |  Carl    |   11022020   |  
   | Jack    |   JEU836   |   4   |  Smith   |   19042020   |   
   | Mandy   |   GER234   |   33  |  Jones   |   09042020   |  
   | Mandy   |   HER575   |   7   |  Jones   |   10052020   |  
   | Jack    |   JEU836   |   4   |  Smith   |   14022020   |   
   | Ryan    |   GER631   |   33  |  Jacque  |   12042020   |  
   | Sarah   |   HER575   |   7   |  Barlow  |   01022019   |  
   | Jack    |   JEU836   |   4   |  Smith   |   14042020   |   
   | Ryan    |   HUH233   |   33  |  Jacque  |   15042020   |  
   | Sarah   |   HER575   |   7   |  Barlow  |   02022019   |

My SQL:

SELECT name, value, num, SecondName, Date
FROM MyTable 
INNER JOIN (SELECT NAME, MAX(DATE) AS MaxTime FROM MyTable GROUP BY NAME) grouped ON grouped.NAME = NAME

WHERE NUM = 33
AND grouped.MaxTime = Date

What I'm doing here, is selecting the table, and creating an INNER JOIN where I'm taking the MAX Date value (the biggest/newest value), and grouping by the Name, so this will return the newest created row, for each person (Name), WHERE the NUM field is equal to 33.

Results:

   | Ryan    |   HUH233   |   33  |  Jacque  |   15042020   |

As you can see, it is returning one row, as there are 3 rows with the NUM value of 33, two of which are with the Name 'Ryan', so it is grouping by the Name, and returning the latest entry for Ryan (This works fine).

But, Mandy is missing, as you can see in my first table, she has two entries, one under the NUM value of 33, and the other with the NUM value of 7. Because the entry with the NUM value of 7 was created most recently, my query where I say 'grouped.MaxTime = Date' is taking that row, and it is not being displayed, as the NUM value is not 33.

What I want to do, is read every row WHERE the NUM field is 33, THEN select the Maximum Time inside of the rows with the value of 33.

I believe what it is doing, prioritising the Maximum Date value first, then filtering the selected fields with the NUM value of 33.

Desired Results:

   | Ryan    |   HUH233   |   33  |  Jacque  |   15042020   |  
   | Mandy   |   GER234   |   33  |  Jones   |   09042020   |

Any help would be appreciated, thank you.

Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Oct 05 '20 at 09:53
Re your goal: This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Oct 05 '20 at 09:53
What DBMS are you asking this for? Please tag SQL questions always with the DBMS you are using. — Thorsten Kettner, Oct 05 '20 at 10:35

score 0 · Answer 1 · answered Oct 05 '20 at 09:51

0

If I folow you correctly, you can filter with a subquery:

select t.*
from mytable t
where t.num = 33 and t.date = (
    select max(t1.date) from mytable t1 where t1.name = t.name and t1.num = t.num
)

answered Oct 05 '20 at 09:51

GMB

216,147
25
84
135

score 0 · Answer 2 · answered Oct 05 '20 at 10:33

Look at your subquery. You want the maximum dates for num 33, but you are selecting the maximum dates independent from num.

I think you want:

select * 
from mytable
where (name, date) in
(
  select name, max(date)
  from mytable
  where num = 33
  group by name
);

SQL SELECT most recently created row WHERE something is true

2 Answers2