join to max value - SQL Server Forums

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

SQL Server Development (2000)

join to max value

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-12-22 : 12:18:34

Hi,
This is my join query in a sql statement.
Looking at table tblFileActivity, I would like to join only to the maximum FileID field in that table.
Can you please alter this statement?
Thanks

INNER JOIN tblFileActivity as fa ON f.FileID = fa.FileID
inner join tblFileAction on fa.ActionID = tblFileAction.ActionID
inner join tblServerShares as ss on f.ServerShareID = ss.ServerShareID

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-22 : 13:07:35

Probably need a little more information. You only want to join one row out of tblFileActivity regardless of any other conditions?

HTH

=================================================================

Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910)

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-12-22 : 16:25:33

Yes, Thanks
Basically something like:
INNER JOIN tblFileActivity as fa ON f.FileID = (select max(fileid) from tblFileActivity )
inner join tblFileAction on fa.ActionID = tblFileAction.ActionID
inner join tblServerShares as ss on f.ServerShareID = ss.ServerShareID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-22 : 16:33:08

INNER JOIN tblFileActivity as fa ON f.FileID = fa.FileID
inner join tblFileAction on fa.ActionID = tblFileAction.ActionID
inner join tblServerShares as ss on f.ServerShareID = ss.ServerShareID
WHERE fa.FileID = (select max(fileid) from tblFileActivity )

Tara

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-12-22 : 16:35:31

Hi,
I don't think this is correct because what I am after is the maximum record for each join.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-22 : 16:36:41

We'll need to see sample data then to visualize this. Only one maximum value for a column exists in a table when done on the whole table, so I'm not sure what you mean.

Tara

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-12-22 : 16:38:13

Ok thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-22 : 17:32:39

First you verify that you only want one row in total; then you say that you want one row for each. It makes it difficult to help when you can't be precise in what you want.

HTH

=================================================================

Sometimes I wonder whether the world is being run by smart people who are putting us on or by imbeciles who really mean it. -Mark Twain, author and humorist (1835-1910)

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-12-23 : 04:33:05

Hi,
Here is a sample data of the table that I would like to join to.
As you can see the FildIDs are repeated. I only want to join to the ActionID field but for the record which has the maximum date for each FileID.
For example for the FileID --> 51019118 I need to join to ActionID 205
for FileID --> 67794566 I need to join to ActionID 200
for FileID --> 67794567 I need to join to ActionID 200

Thanks

Table name --> tblFileActivity

FAID FileID ActionID ActionDate ActionBy

2880211 51019118 100 2004-11-03 16:17:58.047 GBHONC
2880212 51019118 205 2004-11-06 19:54:15.053 GBHONC
2880213 51019118 205 2004-11-18 13:35:52.480 GBHONC
311720 67794566 200 2004-11-09 18:22:53.970 USHOLD
311721 67794566 205 2004-11-16 23:14:26.493 USIGOJ
311722 67794566 200 2004-11-24 22:14:16.980 USHOLD
311723 67794567 200 2004-11-09 17:51:32.653 USHOLD
311724 67794567 205 2004-11-16 23:14:26.493 USIGOJ
311725 67794567 200 2004-11-24 22:14:17.073 USHOLD

This is a simplified version of the join query
inner join tblFile as f on uf.FileID = f.FileID
INNER JOIN tblFileActivity as fa ON f.FileID = fa.FileID
inner join tblFileAction on fa.ActionID = tblFileAction.ActionID

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-12-23 : 05:22:18

This is what I have now.
What do you think?
Thanks
INNER JOIN tblFileActivity as fa ON f.FileID in --= fa.FileID

(select FileID
from tblfileactivity
where
ActionDate in (select max(ActionDate)
from tblFileActivity
where (FileID >= '50086439' AND FileID <= '50086494')
group by FileID)
AND FileID >= '50086439' AND FileID <= '50086494'
group by FileID)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-23 : 07:28:14

looks like to me that you're trying to get the first from each group:
...
inner hoin (select max(ActionDate) as ActionDate, FileID from tblFileActivity group by FileID) t1
on t1.ActionDate = fa.ActionDate and t1.FileID = fa.FileID
...

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources