retrieve max date results

oracle select max date in where clause
sql select max date for multiple records
how to get latest record in sql by datetime
oracle sql select max date for multiple records
mysql max date in where clause
sql select most recent record by date
sql select max date for each id
sql select row with max date

Table A

Group   ID  
1      117  
2     123  
3     117  

Table B

Group ID    Date  
1     120   18/2/2  
1     113   18/2/3  
1     123   18/2/4  
2     113   18/1/1  
2     120   18/4/5  
2     123   18/7/6   
3     113   18/1/1  
3     120   18/4/5  
3     123   18/2/3 

I want to retrieve all those rows where Id in table A = 117 and its corresponding entry in Table B with ID = 123 and if it has maximum date Result should be : 1 123 18/2/4

I think you are looking for

SELECT MIN(T1.[Group]) [Group],
       T1.ID,
       T2.ID,
       MAX(T2.[Date]) [Date]
FROM T1 INNER JOIN T2 ON T1.[Group] = T2.[Group]
WHERE T1.ID = 117
      AND
      T2.ID = 123
GROUP BY T1.ID,
         T2.ID;

Returns:

+-------+-----+-----+---------------------+
| Group | ID  | ID  |        Date         |
+-------+-----+-----+---------------------+
|     1 | 117 | 123 | 04/02/2018 00:00:00 |
+-------+-----+-----+---------------------+

SQL MAX() on date value, SQL MAX() on date value: Here we have discussed the usage of SQL MAX() function SELECT MAX (ord_date) AS "Max Date" FROM orders;. This is the table structure CREATE TABLE `reports` ( `report_id` int(11) NOT NULL auto_increment, `computer_id` int(11) NOT NULL default '0', `date_entered` datetime NOT NULL default '1970-0

Select T1.Group ,T2.ID, T2.Date 
From TableA as T1 INNER JOIN TableB as T2
ON T1.GroupId = T2.GroupId 
WHERE T2.Date = (SELECT MAX(T2.Date))

Can you try this query, it's basically joining two tables with your GroupID as it's relationship between those tables, and after that it's selecting MAX date value from your second table, and in your select you specify what you want to see.

Because you said you want to see Group from first table, ID from second table and Max date from Second table there it is..

Select which has max date or latest date, Your current query is not giving the desired result because you are using a GROUP BY clause on the PERSON_ID column which has a unique value for both    In the case of a simple sheet, like this one, you can easily see the answer, but that's not a solution. Returning the latest date is simple enough. Enter =MAX(range), which in this case is A2:A9.

How to select the line with the max(date)?, SELECT B.* FROM ( select id,max(date) date from table1 group by id ) A INNER JOIN table1 B USING (id,date);. You should create this index to  Using the above output as a derived table, you can then get only rows where date matches max_date: SELECT group, date, checks FROM ( SELECT *, max_date = MAX(date) OVER (PARTITION BY group) FROM table) AS s WHERE date = max_date ; to get the desired result.

First you have wrong Date Format in your TableB and i am wonder is this format in date type column, if no then it would become trouble.

If this already format in date type column then you can try this query

SELECT 
     TA.Group, 
     TB.ID, 
     TB.Max_Date 
FROM 
     TableA as TA 
     INNER JOIN (SELECT 
                    GROUP, 
                    ID, 
                    MAX(DATE) AS Max_Date
                 FROM
                    TableB
                 WHERE
                    ID = '123'
                 GROUP BY
                    GROUP,
                    ID) TB ON TA.Group = TB.Group
WHERE
     TA.ID = 117

And for the Worst Case you in Date column not Date Format. You can try this.

    SELECT 
         TA.Group, 
         TB.ID, 
         TB.Max_Date 
    FROM 
         TableA as TA 
         INNER JOIN (SELECT 
                        GROUP, 
                        ID, 
                        MAX(To_Date(DATE, 'YY/MM/DD')) AS Max_Date
                     FROM
                        TableB
                     WHERE
                        ID = '123'
                     GROUP BY
                        GROUP,
                        ID) TB ON TA.Group = TB.Group
    WHERE
         TA.ID = 117

I am using To_Date function in Postgresql because i don't know what rdbms you are using..

MAX(<date>) / GROUP BY behaviour, The results I get as shown below are somewhat confusing. I do get the latest date but the values for the columns date1, date2, date3 and date4 are not matching  I have a table of data (the db is MSSQL): ID OrderNO PartCode Quantity DateEntered 417 2144 44917 100 18-08-11 418 7235 11762 5 18-08-11 419 9999 60

How to select the last record of a table in SQL?, How can I get last record of a table in SQL? Hello All, I am looking for MAX date from Date column and based on that output table will be display on max date Yes and No is Two filter slicer Below is the sample data ID Name Availability Date 1 ABC yes 16/3/2017 1 DEF no 16/3/2017 2 DEF yes 16/3/2017 3 OPQ yes 15/3/2017 3 OPQ yes 11/3/20

Filtering to the Single Most Recent Date, Select BOTH the home and its max date time, then join back to the top table on BOTH the fields: Want to learn SQL from basics! Here's the right  How to find the earliest or latest date base on criteria in Excel? Let’s say, you have two columns of data which Column A contains the products, Column B includes date, and now, you want to find the earliest or latest date based on the criteria in Column A as following screenshot shown.

How can I SELECT rows with MAX(Column value), DISTINCT by , The SQL MAX function is used to return the maximum value of an expression in a SELECT MAX(aggregate_expression) FROM tables [WHERE conditions]; the max(report_run_date) as maxdate and we've aliased the entire result set as  The array result from the IF function is delivered directly to the MAX function, which returns the maximum value in the array. With MAXIFS In Excel O365 and Excel 2019, the new MAXIFS function can find the maximum value with one or more criteria without the need for an array formula.

Comments
  • Your question is unclear a bit, could you please edit your question and add expected resutlts.
  • Hi, the above query is retrieving all those rows where Id in table A = 117 and its corresponding entry in Table B with ID = 123 and its maximum date in Table B. But i want to retrieve the record where Id in table A = 117 and its corresponding entry in Table B with ID = 123 only if it has maximum date
  • @Cherry what type in date column? and what rdbms you are using here?
  • date is correct for first option not in Date format and i am using SQL
  • @Cherry sql are language not database.. Database like postgresql, mysql, oracle.. So which one you using?