3 Replies - 383 Views - Last Post: 27 October 2017 - 07:41 AM

#1 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 338
  • Joined: 20-May 16

how to get max date by using left joins and multi tables?

Posted 27 October 2017 - 07:04 AM

how to get max date by using left joins and multi tables?

I am using oracle sql

Table name is MyTable
| id | ... |
| 1  | ... |
| 2  | ... |



Table name is 'MyDateTable':
| id | col1                | col2  | col3                |
| 1 | 2017-06-27 00:00:00 | test1 | 2017-08-25 10:45:44 | 
| 1  | 2017-06-27 00:00:00 | test2 | 2017-08-22 16:22:44 | 
| 1  | 2017-05-16 00:00:00 | test3 | 2017-08-29 10:35:38 | 
| 1  | 2017-03-20 00:00:00 | test4 | 2017-05-16 16:41:44 |
| 1  | 2017-05-16 00:00:00 | test4 | 2017-05-16 16:41:57 |
 



select id, ...
from myTable m
left join (select distinct id, max(col1) col1 from MyDateTable group by id order by id desc) dd on dd.id = m.id this is what I want.
left join (select disinct id, col1, col2,col3 from MyDateTable) d on d.id = dd.id and s.col1 = dd.col1 
...


'MyTable' id is primary key
'MyDateTable' id is forgin key

I want to display 'test1' becuase its the lastest date in col1.
Issue is that if I do max than it will return 2 records bc they have the same date in col1.
and I can not use col3 bc max(col3) will return 'test3'.

Thats why i am think to do a max on col1... it will return 2 records.. than I can use max on col3.

This post has been edited by ikhlas06: 27 October 2017 - 07:28 AM


Is This A Good Question/Topic? 0
  • +

Replies To: how to get max date by using left joins and multi tables?

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,342
  • Joined: 12-December 12

Re: how to get max date by using left joins and multi tables?

Posted 27 October 2017 - 07:08 AM

It is not clear what you are trying to achieve because the first table only has one column id, and the second table does not have an id column.

Provide more information about the tables; especially, how are the two tables related?

Something a bit more descriptive that col1, col2, col3 would also help.
Was This Post Helpful? 0
  • +
  • -

#3 ikhlas06   User is offline

  • D.I.C Regular

Reputation: 2
  • View blog
  • Posts: 338
  • Joined: 20-May 16

Re: how to get max date by using left joins and multi tables?

Posted 27 October 2017 - 07:19 AM

I have edit my post. does this help?

This post has been edited by andrewsw: 27 October 2017 - 07:37 AM
Reason for edit:: removed previous quote, use the REPLY button

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6833
  • View blog
  • Posts: 28,342
  • Joined: 12-December 12

Re: how to get max date by using left joins and multi tables?

Posted 27 October 2017 - 07:41 AM

Please, in future, do not edit your post substantially, just use the Reply button (further down the page) to add additional information. It is confusing to readers when you do this - it breaks the flow of the thread.

Quote

I want to display 'test1' becuase its the lastest date in col1.
Issue is that if I do max than it will return 2 records bc they have the same date in col1.

If the latest date appears more than once then what is the logic that determines that you want the value 'test1' rather than 'test2'?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1