Need help with an aggregate function

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

SQL Server 2000 Forums

Transact-SQL (2000)

Need help with an aggregate function

Author

Topic

nigato
Starting Member

2 Posts

Posted - 2008-06-05 : 02:35:14

Hi, I'm hoping someone here can help me with a particular query involving the MIN aggregate.

I have two tables with a one to zero-many relationship, let's call them 'e' and 'er' with 'er' having the many.

I need to create a one to zero-one relationship based on the min date from the er table grouped by e's PK.

Here's what I have so far:
SELECT  e.eID, er.Date
FROM    table e LEFT OUTER JOIN
           ( SELECT  er.eID, MIN(er.Date) AS Date
             FROM    table er
             GROUP BY er.eID) er ON e.eID = er.eID

I need a result with one eID, it's earliest (min) date from table er, which I have already but my issue is then showing additional data from er relating to that particular row.

I'm assuming I need to join er with itself on er.erID ???

If you need more info, please let me know.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-05 : 02:37:50

[code]SELECT e.*
FROM TableE AS e
INNER JOIN (
SELECT eID,
MIN(Date) AS Date
FROM TableE
GROUP BY eID
) AS x ON x.eID = e.eID
WHERE e.Date = x.Date[/code]

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

nigato
Starting Member

2 Posts

Posted - 2008-06-05 : 21:03:20

Excellent, thanks Peso, you're a true wonder! :)

I knew it had to be something simple.

As a side note for anyone else who may look for this solution, my particular case has unique dates per e.eID, yours might not.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources