asked on
MSSQL 2008 Query use query result in new query
Hi. i try to figure this out what kind of syntax to use but cant get this to work.
I want. Search in a database after most freqent ID
then make a new query and search in diffrent database whit the result from the first search as a parameter... please have a look belowe.. I verry new in MSSQL so be nice:D
SELECT DB01.ID, Count(DB01.ID) AS Antal
FROM DB01
where Timestamp BETWEEN '2010-01-13 04:22:53.390' AND '2012-02-13 05:22:53.390'
GROUP BY DB01.ID
HAVING Count(DB01.ID)>1
order by Antal desc
Results I get..
ID Antal
09366 936
33593 368
12118 349
11306 303
10424 233
36535 153
38255 131
35700 130
Then I would like to use
the first result "09366"
and search in a nother db.
ex.
Select *
from DB199
where ID=09366
order by Timestamp asc
I want. Search in a database after most freqent ID
then make a new query and search in diffrent database whit the result from the first search as a parameter... please have a look belowe.. I verry new in MSSQL so be nice:D
SELECT DB01.ID, Count(DB01.ID) AS Antal
FROM DB01
where Timestamp BETWEEN '2010-01-13 04:22:53.390' AND '2012-02-13 05:22:53.390'
GROUP BY DB01.ID
HAVING Count(DB01.ID)>1
order by Antal desc
Results I get..
ID Antal
09366 936
33593 368
12118 349
11306 303
10424 233
36535 153
38255 131
35700 130
Then I would like to use
the first result "09366"
and search in a nother db.
ex.
Select *
from DB199
where ID=09366
order by Timestamp asc
Microsoft SQL Server 2008
Last Comment
kavvis
try
declare @MaxID int
SELECT TOP 1 @MaxID = ID
from
(
SELECT DB01.ID, Count(DB01.ID) AS Antal
FROM DB01
where Timestamp BETWEEN '2010-01-13 04:22:53.390' AND '2012-02-13 05:22:53.390'
GROUP BY DB01.ID
HAVING Count(DB01.ID)>1
) A
order by A.Antal desc
Select *
from DB199
where ID=@MaxID
order by Timestamp asc
ASKER
get some problem
Msg 206, Level 16, State 2, Line 13
Operand type clash: uniqueidentifier is incompatible with int
in the second databse the Id is declared like a uniqueidentifier
I can´t change this :( can I fix this whit your querys?
Msg 206, Level 16, State 2, Line 13
Operand type clash: uniqueidentifier is incompatible with int
in the second databse the Id is declared like a uniqueidentifier
I can´t change this :( can I fix this whit your querys?
Try declaring it as uniqueidentifier
declare @MaxID uniqueidentifier
SELECT TOP 1 @MaxID = ID
from
(
SELECT DB01.ID, Count(DB01.ID) AS Antal
FROM DB01
where Timestamp BETWEEN '2010-01-13 04:22:53.390' AND '2012-02-13 05:22:53.390'
GROUP BY DB01.ID
HAVING Count(DB01.ID)>1
) A
order by A.Antal desc
Select *
from DB199
where ID=@MaxID
order by Timestamp asc
ASKER
The problem is in the first DB it´s an int
and in my second database the id is declared like uniqueidentifier
so thaat´s the problem
and in my second database the id is declared like uniqueidentifier
so thaat´s the problem
Can you check the values of ID in your second table. You do understand that they are not integers but GUID
You will not be able to do any meaningful comparison between the two fields. Your second table ID should also have been an integer, or create a new integer field used for joining the two tables.
You will not be able to do any meaningful comparison between the two fields. Your second table ID should also have been an integer, or create a new integer field used for joining the two tables.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
thank you all for your help.. need to change my question to get this done...
from DB199
where ID=(Your first SQL statment here)
order by Timestamp asc
The first SQL stmt must be modified to return the first row . I would do the following on sybase , not sure abt SQLServer
select top 1 COLUMN_NAME from TABLE_NAME order by COLUMN_NAME desc