October 22, 2008 at 7:05 am
Hi I need to get data out of a table and change the value from one of the coulmns.
Problem is i only want to use the first record for each SSOUsers_RowID, there could be more than one record for SSOUsers_RowID I have tried to use distinct on the select satement but it won't work.
Table
SSOUsers_RowID FamilyType ServiceID Param1 etc
32 3 ATAA 75990010
32 3 ATBB 75990010
56 3 ATCC 72390009
56 3 ATDD 72390009
56 3 ATEE 72390009
My test code as below but only want to get the first record for SSOUsers_RowID
declare @MyTable Table
(
[SSOUsers_RowID] [bigint] NOT NULL,
[FamilyType] [tinyint] NULL,
[ServiceID] [varchar](20) NULL,
[Param1] [varchar](20) NULL,
[Param2] [varchar](20) NULL,
[Param3] [varchar](20) NULL,
[Param4] [varchar](20) NULL
)
insert into @MyTable
Select SSOUsers_RowID,FamilyType,'ATAS',Param1,Param2,param3,param4
from ssouserservices where FamilyType='3'
Thanks
October 22, 2008 at 7:55 am
Answer to my own question used
Select DISTINCT SSOUsers_RowID,FamilyType,'ATAS',Param1,Param2,param3,param4
did work must have had a typo previously
October 23, 2008 at 2:44 am
but your service id will not be able to put into the new table
October 23, 2008 at 6:39 am
Hi No i wanted to change the service id value to ATAS for all with the results from the query
October 23, 2008 at 8:32 am
This would be my solution:
insert into @MyTable
Select SSOUsers_RowID,FamilyType,'ATAS',Param1,Param2,param3,param4
from ssouserservices
where FamilyType='3' AND
SSOUsers_RowID =
(
SELECT MIN(SSOUsers_RowID) MINRowID
FROM ssouserservices
where FamilyType='3'
group by FamilyType, Param1,Param2,param3,param4
)
Hope this helps!
Ronald
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply