how to get the earlier date in SQL?

hi guys,
hope everyone is doing well.
I have a sql statement that gets records based on country but sometimes I would get two or more records for the same country and I'm interested in the earlier date which is a field in the database.

can someone help please?

thank you,

Add an and condition to the where clause and perfrom a sub query to get the Min(date).

Quote:

---

Originally Posted by GaryMazzone

Add an and condition to the where clause and perfrom a sub query to get the Min(date).

---

don't I need to write some kind of IF condition checking for multiple country value then get the earlier date?

I attached a table showing my example. the result should have all countries but min date if douplicate country. please check out and advise.



thank you

Is all you need is the date and the country? What about the other fields? Do you need those too?

-tg

Quote:

---

Originally Posted by techgnome

Is all you need is the date and the country? What about the other fields? Do you need those too?

-tg

---

yes techgnome, I need all fields with the condition in my last post.

thank you,
Waely

Date = (Select Min(Date) From tablename Where CountryName in (Select distinct(CountryName From tableName))

This will probably need to be refined but that is the basics.

Code:

---

SELECT D.MinSdate, TN.NofE, TN. [name], TN.country
FROM TableName TN
INNER JOIN (SELECT Min(Sdate) AS MinSdate, Country FROM TableName GROUP BY Country) D
  TN.Country = D.Country

---

See if that gives you what you need....
It's basically a variation of what Gary supplied.

-tg

Quote:

---

Originally Posted by techgnome

Code:

---

SELECT D.MinSdate, TN.NofE, TN. [name], TN.country
FROM TableName TN
INNER JOIN (SELECT Min(Sdate) AS MinSdate, Country FROM TableName GROUP BY Country) D
  TN.Country = D.Country

---

See if that gives you what you need....
It's basically a variation of what Gary supplied.

-tg

---

hi tg,
I get this error on the last line " Incorrect syntax near 'TN'" what should i do?

thanks

Take out the space

Code:

---

SELECT D.MinSdate, TN.NofE, TN.[name], TN.country

---

I don't have spaces in my codes plus it says line 4. any ideas?


waely

I thought I forgot something:

Code:

---

INNER JOIN (SELECT Min(Sdate) AS MinSdate, Country FROM TableName GROUP BY Country) D
  TN.Country = D.Country

---

should be:

Code:

---

INNER JOIN (SELECT Min(Sdate) AS MinSdate, Country FROM TableName GROUP BY Country) D
  ON TN.Country = D.Country

---

-tg

tg,
it didn't work, I got douplication of my records. I got the earlier date of them though but douplicated :-(

I thought that's what you wanted.... or do you only want rows with duplicates?

I'm confused...

-tg

based on my the images attached earlier I have seven records which two of them are dupilcate (US and HUN) and I wanted my final result to be table with 5 records but with
earlier date for these two countries.

I'm sorry if I confused you ...........

Oh, OK.... then the join needs to be modified a little more:

Code:

---

INNER JOIN (SELECT Min(Sdate) AS MinSdate, Country FROM TableName GROUP BY Country) D
  ON TN.Country = D.Country
    AND TN.Sdate = D.MinSdate

---

-tg