I would like to simplify my query

What I'm trying to do is get the last entry (by date) for a given entity (which should be a single row). The following returned more than one row:

select termId, entityId, max(enddate)
from Terms
where entityId = 1234
group by termId, entityId

This does return me one row:

select *
from Terms
where enddate in (select max(enddate)
from Terms
where entityId = 1234)

What I would like to do this in one select statement. Does anyone have any suggestions?

Quote:

Originally Posted by NamelessNumberheadMan

What I'm trying to do is get the last entry (by date) for a given entity (which should be a single row). The following returned more than one row:

select termId, entityId, max(enddate)
from Terms
where entityId = 1234
group by termId, entityId

This does return me one row:

select *
from Terms
where enddate in (select max(enddate)
from Terms
where entityId = 1234)

What I would like to do this in one select statement. Does anyone have any suggestions?

I think your query is correct. I cant get what you mean for simplify my query?

If you want you have to explain your requirement

I only get the first character of the field returned .. what am i doing wrong ?

here is my business/data object for some reason I only get the first character back, say value is Charlie, I only get C

 
public string GetUserName(long UserId) {try {string userName; DiscussionDB data =new DiscussionDB(); List paramlist =new List(); paramlist.Add(data.CreateParameter("@.UserId", UserId)); paramlist.Add(data.CreateParameter("@.UserName","", ParameterDirection.Output, DbType.String, 20)); data.ExecuteNonQuery("dbo.Discussion_User_Name",ref paramlist); userName = paramlist[1].Value.ToString();return userName; }catch {throw; } }ALTER PROCEDURE [dbo].[Discussion_User_Name]@.UserId bigint,@.UserName varchar(20) outputASSET NOCOUNT ONSET @.UserName = (Select [Name] from Discussion_Member WHERE UserID = @.UserID)if (@.UserNameis null or @.UserName ='')BEGIN SET @.UserName = (Select UserName from Membership_User WHERE UserID = @.UserID)END
 

hello,

It does not seem that thre is anything wrong with the code.

Have you checked the size of the columns "Name" and "UserName" in Discussion_Member and/or Membership_User ?

hope this helps?

regards,

g

|||

If you run the stored procedure using the sql server tools, does it return the correct result?

|||

yes when I run the SP in MS sever 2005 studio, I get the full field ... e.g Charlie

|||

You need to set the size of @.UserName, the default is 1. There's a size property toIDbDataParameter that you need to set (it looks like your DiscussionDB is wrapping IDbDataParameter and some other stuff)

|||

I tried wording your suggestion in my code by I can't seem to figure it out

I was thinking that the 20in the code below was giving the size

paramlist.Add(data.CreateParameter("@.UserName","", ParameterDirection.Output, DbType.String, 20));

|||

I thought I got this working but I was wrong

I am still having this issue, here is my last code

public string GetUserName(long UserId) {try {string userName; DiscussionDB data =new DiscussionDB(); List paramlist =new List(); paramlist.Add(data.CreateParameter("@.UserId", UserId)); paramlist.Add(data.CreateParameter("@.UserName","", ParameterDirection.Output, DbType.String, 15)); paramlist[1].Size = 15; data.ExecuteNonQuery("dbo.Discussion_User_Name_Fetch",ref paramlist); userName = paramlist[1].Value.ToString();return userName; }catch {throw; } }