How to retrieve the last record in a select statement from a #temp table?

Question

How to retrieve the last record in a select statement from a #temp table?

BuilderBits

SSCertifiable

Points: 6071
More actions
December 6, 2005 at 9:26 pm

#166713

Is this querry structured correctly?

for this part of the querry i need to select the LAST UserID from the first slect querry... that is why i am using a #temp talbe...
Another question that i have is the ....c ... a variable that holds the last UserID for the #UsersTempTable?

thanks,
erik..
=========================================================
SELECT SpecialtyName
from Specialty s, UserSpecialty us, (SELECT Userid, max(id) from #UsersTempTable group by Userid) c
Where us.UserID = C and us.SpecialtyCD = s.SpecialtyCD

=============================

create PROCEDURE Admin_SelectAll_w_Generic_111
@Admin_Email Varchar (200), /*Admin Check */
@Admin_FName Varchar (30), /*Admin Check */
@Admin_UserType Varchar (20), /*Admin Check */
@Identity Varchar (20),
@GenericColumn Varchar (200) ,
@GenericValue Varchar (200)
AS
DECLARE @sql VARCHAR(8000)
DECLARE @user-id INT

IF EXISTS (SELECT Email, FirstName, UserType FROM Users
WHERE Email = @Admin_Email AND
FirstName = @Admin_FName AND
UserType = @Admin_UserType)
BEGIN
IF (@Identity <> 'Consumer')
BEGIN
CREATE TABLE #UsersTempTable
(
ID int IDENTITY PRIMARY KEY,
         UserID         [int],
FirstName [varchar](30),
LastName [varchar](30),
CompanyName [varchar](200),
Address1       [varchar](75),
Address2 [varchar](75),
City           [varchar](75),
ActiveInd [int],
Zip    [varchar](10),
WkPhone [varchar](12),
HmPhone [varchar](12),
Fax    [varchar](12),
Email   [varchar](200),
Website [varchar](200),
UserType [varchar](20),
Title   [varchar](100),
Note          [text],
StateCD [char](2),
CountryCD [char](2),
CompanyPhoto [varchar](50),
CompanyDescr [varchar](2000)
)
--Fill the temp table with the Customers data
INSERT INTO #UsersTempTable
(
         UserID,
FirstName,
LastName,
CompanyName,
Address1,
Address2,
City,
ActiveInd,
Zip,
WkPhone,
HmPhone,
Fax,
Email,
Website,
UserType,
Title,
Note,
StateCD,
CountryCD,
CompanyPhoto,
CompanyDescr)
Select Users.UserID,
Users.FirstName,
Users.LastName,
Users.CompanyName,
Users.CompanyPhoto,
Users.CompanyDescr,
Users.UserType,
Users.Website,
Users.ActiveInd,
Users.Address1,
Users.Address2,
Users.City,
Users.Zip,
Users.WkPhone,
Users.HmPhone,
Users.Fax,
Users.Title,
Users.Note,
Users.StateCD,
Users.CountryCD,
Users.Email
From Users
WHERE ' + @GenericColumn + ' = ''' + @GenericValue + '''
SELECT SpecialtyName
from Specialty s, UserSpecialty us, (SELECT Userid, max(id) from #UsersTempTable group by Userid) c
Where us.UserID = C and us.SpecialtyCD = s.SpecialtyCD
END
ELSE
IF (@Identity = 'Consumer')
BEGIN
SET @sql = '
Select Users.UserID,
Users.FirstName,
Users.LastName,
Users.UserType,
Users.ActiveInd,
Users.Address1,
Users.Address2,
Users.City,
Users.Zip,
Users.WkPhone,
Users.HmPhone,
Users.Fax,
Users.Title,
Users.Note,
Users.Email,
StateCD,
CountryCD
From Users
WHERE ' + @GenericColumn + ' = ''' + @GenericValue + ''''
EXEC (@SQL)
End
END
Dam again!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

I suppose that by LAST you mean the last inserted into temporary table. You should get this with the following /*untested*/ select (though I'm not sure whether this is what you are asking):

SELECT SpecialtyName

from Specialty s

join UserSpecialty us on us.SpecialtyCD = s.SpecialtyCD

where us.UserID = (select TOP 1 UserID from #UsersTempTable ORDER BY [ID] DESC)

If this does not help, please explain what's wrong with it and we'll try to come with a better solution.

Question is, do you insert the data into temp table ONLY to get the last one? If yes, then something is wrong...

HTH, Vladan