December 3, 2002 at 9:38 am
How would I get the earliest date for each Case_Number with the following query?
SELECT Case_Table.Case_Number, Note_Table.Date_Entered
FROM Case_Table
INNER JOIN Note_Table ON Case_Table.Case_Number = Note_Table.Case_Number
December 3, 2002 at 10:33 am
How about
SELECT Case_Table.Case_Number, MIN(Note_Table.Date_Entered) AS 'Date_Entered'
FROM Case_Table
INNER JOIN Note_Table ON Case_Table.Case_Number = Note_Table.Case_Number
GROUP BY Case_Table.Case_Number
Far away is close at hand in the images of elsewhere.
Anon.
December 3, 2002 at 11:18 am
I oversimplified my example query. I need to be able to also include 'non-unique' columns. This is probably a better example:
SELECT Case_Table.Case_Number, Note_Table.Note_Type, Note_Table.Date_Entered AS 'Date_Entered'
FROM Case_Table
INNER JOIN Note_Table ON Case_Table.Case_Number = Note_Table.Case_Number
So, if I use the following (based on previous reply), I could get more than one record returned if Note_Type is not unique:
SELECT Case_Table.Case_Number, Note_Table.Note_Type, MIN(Note_Table.Date_Entered) AS 'Date_Entered'
FROM Case_Table
INNER JOIN Note_Table ON Case_Table.Case_Number = Note_Table.Case_Number
where Case_Table.case_number = 15214
GROUP BY Case_Table.Case_Number, Note_Table.Note_Type
I think I need to alias the Note_Table to itself, but am having difficulty with the syntax.
December 4, 2002 at 3:52 am
Try using subquery
SELECT Case_Table.Case_Number, Note_Table.Note_Type, Note_Table.Date_Entered
FROM Case_Table
INNER JOIN Note_Table ON Case_Table.Case_Number = Note_Table.Case_Number
AND Note_Table.Date_Entered = (SELECT MIN(Date_Entered) FROM Note_Table WHERE Case_Number = Case_Table.Case_Number)
WHERE Case_Table.case_number = 15214
Far away is close at hand in the images of elsewhere.
Anon.
December 8, 2002 at 1:48 pm
quote:
How would I get the earliest date for each Case_Number with the following query?SELECT Case_Table.Case_Number, Note_Table.Date_Entered
FROM Case_Table
INNER JOIN Note_Table ON Case_Table.Case_Number = Note_Table.Case_Number
What you need is a self join. Here is some SQL that does this:
select *
from table1 outside
where crdate =
(select min(crdate)
from table1 inside
where inside.casenumber = outside.casenumber
)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply