help with statement - union? temp table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with statement - union? temp table?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-26 : 01:02:23

Hi,

I want to SELECT results across a few tables, and do an order by across these tables too.

For example I have the following queries. From these two queries I want to return the TOP 20 results, ordered by "dateSent DESC"


SELECT  messageToID FROM tblIM WHERE messageFromID = @userID

SELECT  messageTo FROM tblmessage WHERE messageFrom = @userID

What would be the best way to approach this? I want to spend the time to make this as quick as possible :)

Thanks again!
mike123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 01:06:10

[code]
select TOP 20 *
from
(
SELECT messageToID, dateSent FROM tblIM WHERE messageFromID = @userID

union all

SELECT messageTo, dateSent FROM tblmessage WHERE messageFrom = @userID
) d
ORDER BY dateSent DESC
[/code]

KH

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-26 : 01:16:45

quote:

Originally posted by khtan


select TOP 20 *
from
(
    SELECT  messageToID, dateSent FROM tblIM WHERE messageFromID = @userID

    union all

    SELECT  messageTo, dateSent FROM tblmessage WHERE messageFrom = @userID
) d
ORDER BY dateSent DESC

KH

KH

if the table tblIM has more than 20 rows, there is a chance all top 20 rows *might* come from that table only. You could *also* select 10 from each table individually. this is just FYI. IF the results are satisfactory then cool..

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-26 : 01:23:57

But the criteria is that he wants TOP 20 sorted by dataSent, so it doesn't matter from which of the two tables it comes because OP wants order by across the tables, not Top 20 from each table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-26 : 01:40:52

Hi Khtan,

I put it together like this, and it looks great. One requirement I just realized I need is I would like the first column (messageToID) a distinct column. I only want to bring back each messageToID once.

Is this possible with the current structure?

Thanks once again for all the help Khtan! :)
mike123

(edit: removed accidental top 10)


select *
from
(
   
SELECT messageToID, date FROM tblinstantmessage WHERE messageFromID = @userID UNION ALL
SELECT messageFromID,   date FROM tblinstantmessage WHERE messageToID = @userID UNION ALL

SELECT messageTo, date  FROM tblmessage WHERE messageFrom = @userID UNION ALL
SELECT messageFrom, date  FROM tblmessage WHERE messageTo = @userID 
 ) d
ORDER BY date DESC

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-26 : 01:41:54

quote:
Originally posted by harsh_athalye

But the criteria is that he wants TOP 20 sorted by dataSent, so it doesn't matter from which of the two tables it comes because OP wants order by across the tables, not Top 20 from each table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

this is correct, probably why my data looks a little bit off....

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-26 : 01:44:25

Mike123,

TOP clause is meaning without a corresponding ORDER BY clause.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-26 : 02:04:14

Sorry my bad, I pasted that statement by accident. I am running without the TOP clause and still having the same problem with my results. Here is an example of what my results look like. Not sure if I can easily fix this or have to restructure this query?

Thanks again!
mike123

messageToID / DateSent
92776 2007-04-25 22:13:00
22156 2007-04-25 14:14:00
22156 2007-04-25 14:11:00
22156 2007-04-25 11:49:00
808010 2007-04-24 23:56:00
22156 2007-04-24 15:25:00
22156 2007-04-24 14:50:00
22156 2007-04-24 14:06:00
22156 2007-04-24 11:21:00
22156 2007-04-23 20:59:00
739403 2007-04-23 16:26:00
739403 2007-04-23 16:17:00
739403 2007-04-23 16:04:00

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-26 : 19:45:33

quote:
Originally posted by harsh_athalye

But the criteria is that he wants TOP 20 sorted by dataSent, so it doesn't matter from which of the two tables it comes because OP wants order by across the tables, not Top 20 from each table.

Hi harsh_athalye,

Do you know how I would have to restructure this query to get my desired results? You see the problem I am having 100% clearly

Thanks very much! :)
mike123

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-26 : 19:48:56

try a DISTINCT.


SELECT DISTINCT MessageId, DateSent FROM 
(...) 
ORDER BY DateSent DESC

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 19:57:38

OR....

Top X outside the derived table, and UNION only inside.

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-26 : 20:40:49

Hi,

I tried the distinct, but it's still bringing back duplicate "MessageToID" columns. One thing to note that the "messageToID" column has a different "date" value for each row brought back. For example when running the below code I get the following results.

messageToID / date

22156 2007-04-24 15:25:00
22156 2007-04-24 14:50:00
22156 2007-04-24 14:06:00
22156 2007-04-24 11:21:00
22156 2007-04-23 20:59:00



SELECT   DISTINCT(messageToID),date
from
(
   
SELECT   messageToID, date FROM tblinstantmessage WHERE messageFromID = 410  UNION  
SELECT   messageFromID,   date FROM tblinstantmessage WHERE messageToID = 410  UNION  

SELECT   messageTo, date  FROM tblmessage WHERE messageFrom = 410  UNION  
SELECT messageFrom, date  FROM tblmessage WHERE messageTo = 410  UNION  


SELECT commentTo, date FROM tblcomment WHERE commentFromID = 410  UNION  
SELECT  commentFromID, date FROM tblcomment WHERE commentTo = 410  


) d
ORDER BY date DESC

Peso, I believe this query adheres to your suggestion of UNION's inside and TOP outside, but I get an error.

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DISTINCT'.
Server: Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'd'.

I havent really used DISTINCT much maybe I am making an obvious mistake.





 select top 20 DISTINCT(messageToID),date
from
(
   
SELECT   messageToID, date FROM tblinstantmessage WHERE messageFromID = 410  UNION ALL
SELECT   messageFromID,   date FROM tblinstantmessage WHERE messageToID = 410  UNION  ALL

SELECT   messageTo, date  FROM tblmessage WHERE messageFrom = 410  UNION  ALL
SELECT messageFrom, date  FROM tblmessage WHERE messageTo = 410  UNION  ALL


SELECT commentTo, date FROM tblcomment WHERE commentFromID = 410  UNION  ALL
SELECT  commentFromID, date FROM tblcomment WHERE commentTo = 410  


) d
ORDER BY date DESC

Thanks so much for stayin with me on this one!! :)

much appreciated
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 01:07:34

You don't have to use DISTINCT, since the UNION operator takes care of that for you.

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-27 : 01:33:36

Hi Peso,

I think the problem I am having is because I am addting the "date" column into my query. I want my "messageToID" column to not repeat any values. Because I am selecting it with the "date" column as well, I believe the row is still considered unique altho I am getting duplicate "messageToID" rows.

Sorry if I'm being difficult to understand, I know I've posted alot of stuff here and its getting hard for me to follow

I get unique values by running this



select top 20  messageToID  

from
(
   
SELECT   messageToID  FROM tblinstantmessage WHERE messageFromID = 410  UNION  
SELECT   messageFromID FROM tblinstantmessage WHERE messageToID = 410  UNION   

SELECT   messageTo   FROM tblmessage WHERE messageFrom = 410  UNION   
SELECT messageFrom   FROM tblmessage WHERE messageTo = 410  UNION   


SELECT commentTo  FROM tblcomment WHERE commentFromID = 410  UNION   
SELECT  commentFromID FROM tblcomment WHERE commentTo = 410  


) d

However I also want to select the "date" column from each table, and then sort by date outside the derived table.

When I change the query to this, I get duplicates again.




 select top 20  messageToID,date  

from
(
   
SELECT   messageToID, date FROM tblinstantmessage WHERE messageFromID = 410  UNION  
SELECT   messageFromID,   date FROM tblinstantmessage WHERE messageToID = 410  UNION   

SELECT   messageTo, date  FROM tblmessage WHERE messageFrom = 410  UNION   
SELECT messageFrom, date  FROM tblmessage WHERE messageTo = 410  UNION   


SELECT commentTo, date FROM tblcomment WHERE commentFromID = 410  UNION   
SELECT  commentFromID, date FROM tblcomment WHERE commentTo = 410  


) d
ORDER BY date DESC

example data brought back:

messageToID / date (92776 appears 3 times is the problem)

92776 2007-04-26 20:06:00
22156 2007-04-26 19:12:00
224842 2007-04-26 11:03:00
224842 2007-04-26 11:02:00
22156 2007-04-26 09:42:00
92776 2007-04-26 09:42:00
92776 2007-04-25 22:13:00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 01:43:20

Try this

SELECT TOP 20	MessageToID,
		MAX(Date) AS Date
FROM		(   
			SELECT	MessageToID,
				Date
			FROM	tblInstantMessage
			WHERE	MessageFromID = 410

			UNION ALL

			SELECT	MessageFromID,
				Date
			FROM	tblInstantMessage
			WHERE	MessageToID = 410

			UNION ALL

			SELECT	MessageTo,
				Date
			FROM	tblMessage
			WHERE	MessageFrom = 410

			UNION ALL

			SELECT	MessageFrom,
				Date
			FROM	tblMessage
			WHERE	MessageTo = 410

			UNION ALL

			SELECT	CommentTo,
				Date
			FROM	tblComment
			WHERE	CommentFromID = 410

			UNION ALL

			SELECT	CommentFromID,
				Date
			FROM	tblComment
			WHERE	CommentTo = 410
		) d
GROUP BY	MessageToID
ORDER BY	2 DESC

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-27 : 02:50:47

Runs lightening quick, and works perfectly

thanks again Peso!! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources