Showing posts with label combining. Show all posts
Showing posts with label combining. Show all posts

Tuesday, March 27, 2012

Help with combining two queries

I have a stored procedure that needs to retrieve the top 1000 sent items and
the top 1000 received items - each ordered by date. So, effectively, the
most recent 1000 sent items and the most recent 1000 received items. Then, I
need to combine them into one result set and again take the top 1000 items
when ordered by date.
Originally, the stored procedure created a temp table and inserted the
results of each query consecutively, then did a SELECT TOP to get the final
results. Because of high traffic, this is killing the DB server. I suspect
that the queries could be combined into one query. I also tried a UNION of
the two selects, but that doesn't work because I need to do have accurate
results on the subquery first (top 1000 ordered by date). Can anyone help me
determine a more effecient solution, preferrably to combine the two queries
into one? I have slimmed down the two queries significantly to show only the
differences. They are below.
DECLARE @.userName AS VARCHAR(25)
SELECT @.userName = 'MyUserName'
-- Gets the sent items
--
SELECT TOP 1000
@.userName as SenderName,
'Sent' AS SentReceived,
u.[user_name] as ReceiverName
FROM dbo.Email_Type (nolock)
INNER JOIN dbo.Email (nolock) ON dbo.Email_Type.ID = dbo.Email.Type
INNER JOIN dbo.Email_Folders (nolock) ON dbo.Email.Folder =
dbo.Email_Folders.ID
RIGHT OUTER JOIN [Profile].dbo.Contact_history ch (nolock) ON dbo.Email.ID =
ch.source_id_guid
INNER JOIN [Profile].dbo.user_profile u (nolock) ON ch.[contact_user_id] =
u.[user_id]
WHERE
ch.[user_id] = @.UserID
ORDER BY
ch.createstamp DESC
-- Gets the received items
--
SELECT TOP 1000
u.[user_name] as SenderName,
'Received' AS SentReceived,
@.userName as ReceiverName
FROM dbo.Email_Type (nolock)
INNER JOIN dbo.Email (nolock) ON dbo.Email_Type.ID = dbo.Email.Type
INNER JOIN dbo.Email_Folders (nolock) ON dbo.Email.Folder =
dbo.Email_Folders.ID
RIGHT OUTER JOIN [Profile].dbo.Contact_history ch (nolock) ON dbo.Email.ID =
ch.source_id_guid
INNER JOIN [Profile].dbo.user_profile u (nolock) ON ch.[user_id] =
u.[user_id]
WHERE
ch.[contact_user_id] = @.UserID
ORDER BY
ch.createstamp DESCHello, karch
You probably want a query like this:
SELECT TOP 1000 *
FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
UNION ALL
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) x
ORDER BY TheDate
However, I think the above query will give the same results as this
query (as long as TheDate is unique among all rows):
SELECT TOP 1000 *
FROM (
SELECT <your columns>
FROM <sent items>
UNION ALL
SELECT <your columns>
FROM <sent items>
) x
ORDER BY TheDate
Razvan|||Yes, but I dont think you can have ORDER BY in the subqueries if you UNION -
I tried this approach and received an error. But, yes, logically that is
what I want to accomplish.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1146597584.838889.18770@.v46g2000cwv.googlegroups.com...
> Hello, karch
> You probably want a query like this:
> SELECT TOP 1000 *
> FROM (
> SELECT TOP 1000 <your columns>
> FROM <sent items>
> ORDER BY TheDate
> UNION ALL
> SELECT TOP 1000 <your columns>
> FROM <sent items>
> ORDER BY TheDate
> ) x
> ORDER BY TheDate
> However, I think the above query will give the same results as this
> query (as long as TheDate is unique among all rows):
> SELECT TOP 1000 *
> FROM (
> SELECT <your columns>
> FROM <sent items>
> UNION ALL
> SELECT <your columns>
> FROM <sent items>
> ) x
> ORDER BY TheDate
> Razvan
>|||In this case, try another level of subqueries:
SELECT TOP 1000 *
FROM (
SELECT * FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) a
UNION ALL
SELECT * FROM (
SELECT TOP 1000 <your columns>
FROM <sent items>
ORDER BY TheDate
) b
) x
ORDER BY TheDate
Razvan

Help with combining Sql Statements

I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two).

insertString ="Insert comments (uID) Select uID FROM users WHERE uName = @.uName"

insertString2 ="INSERT comments (eventID, text) VALUES ( @.eventID, @.comment)"

I have tried:

Insert comments (uID, eventID, text)SELECT uID FROM users WHERE uName = @.uNameVALUES(uID, @.eventID, @.comment)

Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated!

Thanks in advance.

Try it like this:

INSERT INTO comments (uID, eventID, [text])SELECT uID,@.eventID, @.comment FROM users WHERE uName = @.uName

|||

Spot on, thank you very much!

Help with combining data from multiple rows into one column in a view

Hi, I am stumped and was hoping someone could help me out. Any help is
appreciated.

I have a view that looks sort of like this (but with a lot more
entries of course)

UniqueIdentifyierColumn1Column2
1 9999 100
2 9999 200
3 9999 300

What I want to do is to add a column to the view that will contain a
list of the values from column 2 where column 1 is the same.

UniqueIdentifyierColumn1Column2Column3
1 9999100100, 200, 300
2 9999200 100, 200, 300
3 9999300100, 200, 300(gaholmes@.comcast.net) writes:

Quote:

Originally Posted by

Hi, I am stumped and was hoping someone could help me out. Any help is
appreciated.
>
>
I have a view that looks sort of like this (but with a lot more
entries of course)
>
>
UniqueIdentifyier Column1 Column2
1 9999 100
2 9999 200
3 9999 300
>
>
What I want to do is to add a column to the view that will contain a
list of the values from column 2 where column 1 is the same.
>
UniqueIdentifyier Column1 Column2 Column3
1 9999 100 100, 200, 300
2 9999 200 100, 200, 300
3 9999 300 100, 200, 300


This may not be doable in a view, particularly if you are on SQL 2000.

SQL Server MVP Anith Sen has a collection of methods to attack this
problem at http://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx