Solved

MSSQL 2008 Query use query result in new query

Posted on 2012-03-13
8
177 Views
Last Modified: 2012-04-27
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
0
Comment
Question by:kavvis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 12

Expert Comment

by:Sharon Seth
ID: 37713878
Select *
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
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 37713886
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

Open in new window

0
 

Author Comment

by:kavvis
ID: 37713914
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?
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 37713990
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

Open in new window

0
 

Author Comment

by:kavvis
ID: 37714141
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
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 37714204
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.
0
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37714213
<<The problem is in the first DB it´s an int
and in my second database the id is declared like uniqueidentifier<<
Then I think the Id's don't match in any way.  Just take a select top 10 ID from both tables .... and tell us how those 2 ever will match.

 Probably you should loock for a name or a description that can replace the id for a match in your sql
0
 

Author Closing Comment

by:kavvis
ID: 37901109
thank you all for your help.. need to change my question to get this done...
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
find SQL job run average duration 24 80
SQL Sum of items in two tables not equal. 5 64
SQL syntax question 6 74
Creating a View from a CTE 15 48
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question