SELECT b.Top_state, b.Tot_State, c.School_name, c.Tot_marks AS [Tot_School]

FROM (

SELECT TOP 5 a.Top_state, SUM(a.Tot_marks) AS [Tot_State]

FROM Tresult a

GROUP BY a.Top_state

ORDER BY SUM(a.Tot_marks) DESC) b

INNER JOIN Tresult c ON c.Top_state = b.Top_state

AND c.School_name...

As already mentioned, different table names and syntax error

you need something like this

set @sql = replace(@sql,' from ',' into ##mytemp from ') + '; SELECT * from ##mytemp'

And I presume...

First look at what costs the most in the plan

If you look at the plan for the first select query there are 3 table scans, which means that each row...

create table #temp (minval int, maxval int, series int)

insert into #temp

select a.value,b.value,count(*)

from [Table] a

cross join [Table] b

inner join [Table] c on c.value between a.value and b.value

where b.value > a.value

group...

select top 1 a.value,b.value,count(*)

from [Table] a

cross join [Table] b

inner join [Table] c on c.value between a.value and b.value

where b.value > a.value

group by a.value,b.value

having count(*) = b.value-a.value+1

order by count(*) desc

p.s....

if an error occurred and the linked server was not deleted.

Of course you could make the linked server permament, making sure you have the correct security set.

Alternatively, read OPENROWSET in...

SELECT * FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\temp\book1.xls;HDR=YES', [sheet1$])

Change to the path for the xls file and use HDR=NO if there are no headers in the file (in which case the...

In addition, thought I would look further into this. It is a shame that MS did not create xp_direxists and documented these Extended procedures. So, I wrote my own xp_direxists...

CREATE TABLE #temp (FileExists int, IsDirectory int, ParentDirExists int)

INSERT INTO #temp

EXEC master..xp_fileexist 'C:\windows'

IF EXISTS(SELECT IsDirectory FROM #temp WHERE IsDirectory=1)

PRINT 'windows exists'

ELSE

PRINT 'not exists'

DROP...

or

select t1.City, count(t2.place) as [no_of_events]

from (select distinct c.City from Test1 c) t1

left outer join Test2 t2 on t2.Place = t1.City

group by t1.City

order by t1.City

if all the cities need to be...

Thank you Jesper, just reciprocating the favour

Agree also, the nearest I came up with is

select t1.City, count(*) as [no_of_events]

from (select distinct c.City from Test1 c) t1

inner join Test2 t2 on t2.Place = t1.City

group by t1.City

order by...

Few observations, good practice

use ansi joins

update l

set l.rollStatus = t.rollStatus, l.Form = t.Form

from liveDB..tblstudent l

inner join testDB..tbl1student t on t.studentID = l.studentID

always declare column names instead of * where possible

use not...

I think it should be one of these two... maybe

select t1.city,

case when max(t3.no_of_games) is null then null else t1.[name] end as [name],...

Oooops!

Missed that, glad someone noticed