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.
Author |
Topic |
ashchow
Starting Member
1 Post |
Posted - 2013-07-02 : 19:11:11
|
Im new to SQL and have a question.I have to fetch the count of rows from table 1 that has a column with values from table 2. My inner query will return a single column (using top 1) and my Parent SQL should return the number of rows that have a column value returned from my inner SQL.I have my sql like this.Select count(*) TotalRowsFrom Table1Where Table1.MessageHeaderID = (Select top 1 Table2.ID From Table2 Where Table2.MessageId = '1S00012' Order By Table2.CreatedDate Desc)This gives an error - Incorrect syntax near the keyword 'top'. Please help |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-02 : 20:01:20
|
Try this:[CODE]Select count(*) TotalRowsFrom Table1 TWhere T.MessageHeaderID = (Select top 1 T2.ID From Table2 T2 Where T2.MessageId = '1S00012' Order By T2.CreatedDate Desc)[/CODE]Which version of SQL Server are you using? |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-02 : 20:01:28
|
Try this:[CODE]Select count(*) TotalRowsFrom Table1 TWhere T.MessageHeaderID = (Select top 1 T2.ID From Table2 T2 Where T2.MessageId = '1S00012' Order By T2.CreatedDate Desc)[/CODE]Which version of SQL Server are you using? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-03 : 01:14:12
|
[code]Select count(*) TotalRowsFrom Table1 t1CROSS APPLY(Select top 1 ID From Table2Where MessageId = '1S00012'AND t1.MessageHeaderID = IDOrder By Table2.CreatedDate Desc)t2[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|