Fetch the MAX(date) from table1 which is < MIN(date) from table 2

mysql select row with max date
how to get latest record in sql by datetime
mysql max date
mysql max date in where clause
mysql get latest record by date group by
sql select max date for multiple records
php mysql get latest date
mariadb max(date)

Here I have a Table1(Master table(AAA)) and Table2(sub_master table(BBB)). I already fetch some data from AAA to BBB by using some condition.

(Table1 AAA)

ID          Date1                   Grade1
1           2013-07-12 00:00:00.000 1 
1           2014-04-02 00:00:00.000 1 
1           2014-12-29 00:00:00.000 NULL
100000      2012-10-01 00:00:00.000 NULL
100000      2015-10-08 00:00:00.000 NULL
100000      2017-01-10 00:00:00.000 1 
100000      2018-05-29 00:00:00.000 2 

Table 2 BBB

ID          Date2                   Grade2
1           2013-07-12 00:00:00.000 1 
1           2014-04-02 00:00:00.000 1 
100000      2017-01-10 00:00:00.000 1 
100000      2018-05-29 00:00:00.000 2 

Now i want to fetch the another few data from AAA to BBB using the another condtion.

choose the MIN(date2) and find the MAX(date1) < MIN(date2) and WHERE Grade1 IS NULL

Which means the OP should be like

100000      2015-10-08 00:00:00.000 NULL

Please help me solve this problem Thanks in Advance!

SELECT *
FROM   (
           -- Get Max of Date1 with Grade1 is null vallue
           SELECT ID, Date1 = MAX(Date1)
           FROM   Table1
           WHERE  Grade1 IS NULL
           GROUP BY ID
       ) t1
       INNER JOIN
       (
           -- Get Min of Date2
           SELECT ID, Date2 = MIN(Date2)
           FROM   Table2
           GROUP BY ID
       ) t2
       ON  t1.ID = t2.ID
WHERE  t1.Date1 < t2.Date2

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  Fetch max date and other field from the same row of a table grouped by other fields in SAP HANA. You have done partially correct but just missed one step further to get the desired result. The case where after grouping you need to pick one of the rows from the results, use rank number or rank function.

Try like below

 with 
  t1 as    
 (
   select id,max(date1) as date1 from AAA where grade1 is null
   group by id
  ), 
  t2 as

  (
  select id, min(date2) as  date2 from BBB group by id
  ) select t1.*,t2.*
     from t1 join t2 on t1.id=t2.id
     where t1.date1<t2.date2

How to Leverage an Under-Utilized SQL Technique to Find Max , to let me quickly get the max change date from nine columns across nine FROM (SELECT column1 AS [Date] FROM table1 where table1. Max Date ----- 20-OCT-08 SQL MAX() on date value with where . To get data of 'ord_num', 'ord_amount', 'ord_date', 'agent_code' from the 'orders' table with the following conditions - 1. 'ord_date' is equal to the maximum 'ord_date', 2. maximum 'ord_date' from those agents whose 'agent_code' is 'A002', the following SQL statement can be used :

Select Max(Date1) from AAA where Date1 < (Select MIN(DATE2) from BBB) AND GRADE1 is NULL

[ORACLE] JOIN two tables but only if max(date) in table2? : SQL, What I want is to select all columns and rows from Table1, plus all columns from Table2 I can select just the Fee_Due column, by using a sub- SELECT MAX(​DATE) So just to re-iterate, my goal is to retrieve every single column from both​  SELECT * FROM ( SELECT ID, MAX(Date1) FROM Table1 WHERE Grade1 IS NULL GROUP BY ID ) t1 INNER JOIN ( SELECT ID, Date2 = MIN(Date2) FROM Table2 GROUP BY ID ) t2 ON t1.ID = t2.ID and t1.Date1 < t2.Date2

You can do it by translating your English directly into sql:

SELECT TOP 1 * FROM aaa
WHERE date1 < (SELECT MIN(date2)FROM bbb) and grade1 is null
ORDER BY date1 desc

The nuts and bolts are from your English requirement: they get all rows with a date less than the min from table 2. The row having the max date is the top 1 row ordered by date descending

Second latest date in MySQL - Databases, Hi Expert, In MySQL, how can I get second latest date? I have the following table and data: TABLE 1 and 2: Table1: ID, DNUMBER, FDATE 1, 1234, 2014-01-01  DATA : v_date TYPE sy-datum. Then use the below select query to fetch the maximum date from the table. While writng the select query please take care of the spaces between the parentheses. SELECT MAX( ADKY ) FROM <table_name> INTO v_date. WRITE : v_date. Regards, Riju Thomas.

SELECT *
FROM   (
           SELECT ID, MAX(Date1)
           FROM   Table1
           WHERE  Grade1 IS NULL
           GROUP BY ID
       ) t1
       INNER JOIN
       (
           SELECT ID, Date2 = MIN(Date2)
           FROM   Table2
           GROUP BY ID
       ) t2
       ON  t1.ID = t2.ID
and t1.Date1 < t2.Date2

Min and max date of overlapping date ranges, The MAX function calculates the largest (latest) date from dates in column End: in Excel Table named Table1. MAX(Table1[End:]) becomes. Get single records when duplicate records exist. If you have been doing SQL development for a while, you probably have come across this common scenario in your everyday job - Retrieving a single record from a table when there are multiple records exist for the same entity such as customer.

Solved: Find the min and max date, The first solution is through a calculated column and the second is via a measure. Is Latest Row Filter = VAR LatestDate = MAXX(FILTER('Table1','Table1'[Type] =  From the description of the problem you want to find the max date for a given month and year from the data table. 1. Create a calculated column YearMonth = Year(dataTable[Date]) * 100 = Month (dataTable[Date]) 2. Create another caculated column. MaxDate = CALCULATE(MAX(dataTable [Date]),FILTER(dataTable,[YearMonth]=EARLIER(dataTable [YearMonth]))) 3.

Solved: Group by all columns and filter on Max Date, I have the following dataset, I should filter on records and display on the max date​. return IF(MAX(Table1[Date])>=pre && MAX(Table1[Date])<=sele && MAX(​Table1[Date])=maxdate,1,0). Capture.PNG. For more I hope you get an answer​! So I wanted to figure out what was the last date a given broker had activity in 3 different tables. We keep both entry date and update date in the tables, but we decided to use the entry date rather than the update date for this particular report since it was about when they had been active as brokers not when they had updated the tables.

Solved: Filter Flow for only dates after max date, Then get the data in the CDS, traverse table1, and see if Table1.CreatedDate is larger than the largest Date obtained before. If so, store this record in the Sql table.

Comments
  • the OP is from which table ?
  • @Squirrel From AAA