Two table - one to many - need sort on the date!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Two table - one to many - need sort on the date!

Author

Topic

splanton
Starting Member

3 Posts

Posted - 2008-04-08 : 13:47:35

Hi guys,
I a a relative newbie to SQL but this has got me stumped!

I have two tables in a one to many rlationship:

Table1 (one)
TableID Description
1 entry 1 desc
2 entry 2 desc
...

Table2 (many)
Table2ID Table1ID Date
1 1 27/3/2008
2 1 28/3/2008
3 1 29/3/2008
4 2 29/3/2008
5 2 30/3/2008
...

I need to retrie table1 and sort it on the highest date of table2.
What I am expecting back is this:

Description Date
entry 2 desc 30/3/2008
entry 1 desc 29/3/2008

I could really use some help on this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 13:54:14

SELECT t1.Description, MAX(t2.Date) AS Date
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.Table1ID = t1.TableID
GROUP BY t1.Description
ORDER BY MAX(t2.Date) DESC

E 12°55'05.25"
N 56°04'39.16"

splanton
Starting Member

3 Posts

Posted - 2008-04-08 : 14:27:21

Peso - you are a genius - it worked!

Is it easy to exand this query to include extra fields from table2 (e.g. FirstName)
I have attempted to add it here:

SELECT t1.Description, t2.FirstName, MAX(t2.Date) AS Date
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.Table1ID = t1.TableID
GROUP BY t1.Description
ORDER BY MAX(t2.Date) DESC

ut all I got was an error:

expression 'FirstName' as part of an aggregate function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 14:40:59

You have to GROUP BY the FirstName as well.

E 12°55'05.25"
N 56°04'39.16"

splanton
Starting Member

3 Posts

Posted - 2008-04-08 : 14:52:42

Got it! I have been sweating hours over this - Thank you again. I an go eat now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources