top most record from the table in inner join

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 2005 Forums

Transact-SQL (2005)

top most record from the table in inner join

Author

Topic

blast
Starting Member

9 Posts

Posted - 2007-01-03 : 14:53:01

i have 2 tables in join and i need to take only the latest record , i'm not bale to use top feature in
the join table

select a.t1, b.t2, c.t3 from table t1
inner join table t2 in a.t1= b.t2
where
c.t3 in('A', 'B')
order by c.t3, a.t1 asc

now the result has two or more issues pulling from t2 table, i just want a single top most record to be pulled
please, help me....i tried a lot with (top, first, inner select statement)
i think im totally wrong...please help thanks so much for your help in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 14:55:51

What is "latest record"?

Also, if you want your problem solved faster, please provide some same data too and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

blast
Starting Member

9 Posts

Posted - 2007-01-03 : 15:04:24

thks for your immediate response

im getting output like this-- for each id the inner joining table
has several subtransactions for that is so...there are number of entries for the same id....but i wanted the top record means the latest record from that table

a b c
12 12 A
12 12 B
12 12 A
14 14 A
14 14 B
16 16 B
16 16 B
16 16 A
16 16 A

I WANTED A RESULT SOMETHIGN LIKE THIS

a b c
12 12 A
14 14 A
16 16 B

thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 15:35:08

select col1, col2, max(col3)
from <YourTableNameHere>
group by col1, col2
order by col1, col2

If this is not good enough, you must provide some kind of timeline instrument to the table, such as an identity column, or datetime information.

Peter Larsson
Helsingborg, Sweden

blast
Starting Member

9 Posts

Posted - 2007-01-03 : 15:51:04

select col1, col2, max(col3)from <YourTableNameHere>group by col1, col2 order by col1, col2---
this will not work i have two tables joining and i wanted the top most record in the inner table

the similarity between these two tables is the issue no only ...there is last modification date filed in inner joining table..but there are more than on entry filed on the same date ...so no luck using it...i need to pick the top most record from the joining table

please..i would appreciate any help thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 02:52:49

Post DDL for tables here.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources