help with query (distinct search)

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 query (distinct search)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-04 : 21:05:29

Hi,

I have a table that I am trying to do a search on. I want it to bring back a UNIQUE list of the column "messageFrom" with the JOINED column "nameOnline", but I am having problems.

I have a couple queries below that I am getting close to the desired results with, but not exactly what I need.

This query brings back the desired results, however I can't figure out how to do an ORDER BY on the 'date' column.

Any help is much appreciated!! Please let me know if I can post any other info.

Thanks!
mike123

Query#1


SELECT distinct(messageFrom),UD.nameOnline FROM tblMessage MES
JOIN tblUserDetails UD on UD.userID = MES.MessageFrom
WHERE messageTo =500AND deletedByRecipient = 0

With this query below, I get the correct ORDER BY but multiple records for "messageFrom" are brought back when I need it distinct. Also the "Date" column is not needed, I just brought it back so the group by would function.

Query#2


select distinct(messageFrom),MES.date,UD.nameOnline 
FROM tblMessage MES 
JOIN tblUserDetails UD on UD.userID = MES.messageFrom 
WHERE messageTo =500 AND deletedByRecipient = 0 
GROUP BY messageFrom,MES.date,UD.nameOnline


ORDER BY MES.date DESC


 
CREATE TABLE [dbo].[tblUserDetails](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[NameOnline] [varchar](15) NULL)


CREATE TABLE [dbo].[tblMessage](
	[MessageID] [int] IDENTITY(1,1) NOT NULL,
	[MessageFrom] [int] NOT NULL,
	[MessageTo] [int] NOT NULL,
	[Message] [varchar](1500) NULL,
	[prevMessage] [varchar](500) NULL,
	[Subject] [varchar](50) NULL,
	[date] [smalldatetime] NULL,
	[Checked] [tinyint] NULL,
	[deletedbySender] [tinyint] NULL,
	[deletedbyRecipient] [tinyint] NULL 
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-04 : 21:32:33

messageFrom is from which table ?

KH
[spoiler]Time is always against us[/spoiler]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-09-04 : 21:49:40

can you explain more?

I don't understand the question

Are you looking for something like this?

SELECT mes.messageFrom,UD.nameOnline
FROM (Select Row_Number() over (Order by a.date desc) as RowID ,* from tblMessage a) MES
Inner JOIN tblUserDetails UD
on UD.userID = MES.MessageFrom
and mes.ROWID = 1
WHERE messageTo =500 AND deletedByRecipient = 0

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-04 : 22:28:49

quote:
Originally posted by khtan

messageFrom is from which table ?

KH
[spoiler]Time is always against us[/spoiler]

Hey Khtan,

Sorry I mistyped a column name, the original post is fixed.

Thanks very much!!
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-05 : 00:33:46

quote:
Originally posted by Vinnie881

can you explain more?

I don't understand the question

Are you looking for something like this?

SELECT mes.messageFrom,UD.nameOnline
FROM (Select Row_Number() over (Order by a.date desc) as RowID ,* from tblMessage a) MES
Inner JOIN tblUserDetails UD
on UD.userID = MES.MessageFrom
and mes.ROWID = 1
WHERE messageTo =500 AND deletedByRecipient = 0

Hey Vinnie881,

I can't quite follow what this is, but I'm pretty sure its not it. I ran it and its taking a huge amount of time to run when my near results are pretty instant. I'm not able to complete the query as I aborted after 30 seconds.

cheers,
mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-05 : 00:51:48

please also provide some sample data and the expected result

KH
[spoiler]Time is always against us[/spoiler]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-09-05 : 01:28:37

try this, I accidently left out the partition by clause in my last post.


SELECT mes.messageFrom,UD.nameOnline ,mes.date
FROM 
	(
		Select  Row_Number() over (partition by a.MessageFrom Order by a.date desc) as RowID,*
		from	tblMessage a
	) MES
Inner JOIN 
	tblUserDetails UD 
on		UD.userID = MES.MessageFrom
and		mes.ROWID = 1
WHERE	
		mes.messageTo = 500 
	AND deletedByRecipient = 0

rcurrey
Starting Member

30 Posts

Posted - 2007-09-05 : 08:25:46

Try this:

SELECT distinct(messageFrom),UD.nameOnline FROM tblMessage MES
JOIN tblUserDetails UD on UD.userID = MES.MessageFrom
WHERE messageTo = 500 AND deletedByRecipient = 0
ORDER BY MES.date

You don't need to return the column in order to sort by it.

If that doesn't work, try:

SELECT max(MES.messagefrom),UD.nameonline
FROM tblMessage MES JOIN tblUserDetails UD
on MES.MessageFrom = UD.UserID
WHERE messageTo = 500 AND deletedByRecipient = 0
GROUP BY MES.messageFrom

Hope this helps

Thanks,
Rich

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-09-05 : 13:12:07

Mike, can you please clarify whether you are looking to accomplish the following?

TBLMessage can have numorus rows with where the MessageFrom is = to the UD.userID, but you only want to return the record that matches which has the highest date.

If this is what you are doing, then there are a lot of ways to do this. The query I posted previously, I believe will work (make sure you use the one with the partition by, the first one was incorrect) or you can always use the top clause as well.


SELECT	UD.nameOnline
		,ud.UserID
		,(
			select top 1 mes.Date
			From	tblMessage MES
			WHERE	mes.messageTo =500 
			AND mes.deletedByRecipient = 0 
			and MES.MessageFrom = ud.UserID
			order by mes.date desc
		 )  as Date
FROM 
tblUserDetails UD

Check the execution plan on both to see what runs better for you.
If this is not what you are looking to accomplish then please clarify your question.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 13:15:05

I think this is the fifth post/thread/topic about same thing.

E 12°55'05.25"
N 56°04'39.16"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-05 : 15:38:18

select messageFrom,max(MES.date) as LastDate,UD.nameOnline
FROM tblMessage MES
JOIN tblUserDetails UD on UD.userID = MES.messageFrom
WHERE messageTo =500 AND deletedByRecipient = 0
GROUP BY messageFrom,UD.nameOnline
order by max(MES.date) asc

--------------------
keeping it simple...

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-09-05 : 15:58:04

Hey Guys,

Thanks for all the support I thought this thread was dead. I realized the answer I thought was correct, no longer was so I opened a new thread.

This is actually what I was looking for..

SELECT
A.voteForID AS voteForID_1,
A.voterID AS VoterID_1,
A.date AS Date_1,
A.points AS points_1,
B.voteForID AS voteForID_2,
B.voterID AS VoterID_2,
B.date AS Date_2,
B.points AS points_2

FROM tblUserVote A, tblUserVote B

WHERE (A.voteForID = 500 and A.voterID = 1000) and (B.voteForID = 100 and B.voterID = 500)

thanks to everyone!
mike123

(this can be locked/deleted if deemed necessary)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources