how to get the earlier date in SQL?

[waely]

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,

[GaryMazzone]

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

[waely]

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

[techgnome]

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

-tg

[waely]

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

[GaryMazzone]

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.

[techgnome]

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

[waely]

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

[Hack]

Take out the space

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

[waely]

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


waely

[techgnome]

I thought I forgot something:

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

should be:

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

-tg

[waely]

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

[techgnome]

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

I'm confused...

-tg

[waely]

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 ...........

[techgnome]

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

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