January 24, 2010 at 11:52 pm
Hi
I have a table CREATE TABLE [dbo].[Test](
[GroupID] [varchar](50) NULL,
[Rank] [bit] NULL,
[RealRank] [bit] NULL
) ON [PRIMARY]
and data like:
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 1,1)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 1,0)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 1,0)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 1,1)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 2,1)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 2,1)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 2,0)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 3,1)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 3,0)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 3,1)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 3,1)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 4,0)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES (4,0)
INSERT INTO [SFDC60].[dbo].[Test] ([GroupID] ,[Rank]) VALUES ( 4,0)
I need to get a script to update the Field Real Rank to TRUE , If any of the value of the filed Rank is true in the group. The group is created by group by GroupID.
so for the above data, I need to get the value TRUE for all the GroupID except GroupID4.
Any help is really great ful.
January 25, 2010 at 12:17 am
UPDATE Test SET RealRank = 1
WHERE GroupID IN (SELECT GroupID FROM dbo.Test WHERE [Rank] = 1)
Thanks for the sample data. In future could you perhaps leave off the database name on the insert statements. I don't have the same DBs as you do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2010 at 5:26 am
Thank you for your reply.
Thats giving me the real data i want.
But
When I tried all values for Rank as false, its giving me NULL as value.
How can I get it False over there ?
Another problem, I want to update another field in that table as well with the same way, I didnt actually added that in the samle data.
I need to update the [Newcoursename] as the first avilable corusename whcih is grouped by startdate.
that means, I need to get the min(Coursename) group by startdate as the newcoursename.
So i was thinking to get it as a loop for each gorup.
or any other way?
sampel data is:
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (1,1,'course1','1/3/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (1,0,'course2','1/5/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (1,1,'course4','1/1/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (2,1,'course5','1/3/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (2,0,'course1','1/7/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (2,0,'course3','1/3/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (2,0,'course5','1/1/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (3,1,'course1','1/3/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (3,0,'course2','1/5/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (4,1,'course1','1/3/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (4,1,'course3','1/7/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (5,0,'course4','1/1/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (5,1,'course1','1/3/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (1,1,'course1','1/3/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (1,1,'course1','1/
Thanks
January 26, 2010 at 7:30 am
Biz (1/26/2010)
Thank you for your reply.Thats giving me the real data i want.
But
When I tried all values for Rank as false, its giving me NULL as value.
How can I get it False over there ?
Best I think would be to define the column as NOT NULL DEFAULT 0. That way the column will be 0 unless updated to 1.
So i was thinking to get it as a loop for each gorup.
or any other way?
sampel data is:
No need for a loop. A subquery with a group by should work.
Sorry, don;t have time to write it for you. See if you can write a query that, for each group, returns the course that you want. (using group by and aggregation), then use that within the update statement. It's pretty basic SQL
p.s. Please can you edit your last post and change the INSERT INTO so that it does not reference a database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2010 at 7:21 am
I tried this, but giving error because its giving error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Warning: Null value is eliminated by an aggregate or other SET operation.
The script is:
UPDATE Test SET [CourseName]=
(select Min([coursename]) from dbo.test group by Groupid,[startdate])
January 28, 2010 at 7:40 am
You're missing the relationship between the table being updated and the subquery. You need to say which row should be updated with which value. That's currently missing.
UPDATE Test SET [CourseName] = MinCourse
FROM
Test Inner Join
(select Min([coursename]) MinCourse , GroupID, StartDate from dbo.test group by Groupid,[startdate]) sub
ON Test.GroupID = sub.GroupID and Test.StartDate = sub.startdate
Sure you want to group by the startdate? Does that as a query produce the correct result? If not, get the below query right before you try updating.
select Min([coursename]), GroupID, StartDate from dbo.test group by Groupid,[startdate]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2010 at 1:33 am
Hi,
Thank you for your reply.
I want to get the first avialable course in that group. so i thought if we group by coursestartdate, I can take the min value.
Is there any other way to get the first value of that duplcated groups as per coruse start date...??
January 29, 2010 at 5:19 am
No, if you group by the course date, you're asking for the minimum course name PER course date.
Your requirements are not clear.
Do you want the minimum course name per date? (so for each unique date, the minimum course name (string-comparison minimum))
Do you want the minimum course name per group id?
Do you want the course name that corresponds to the earliest course date per group id?
Something else?
What would really help here would be for you to go over the sample data that you posted and, based on that, give us an example of the results you want from that data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2010 at 5:22 pm
Hi ,
I need:
The course name that corresponds to the earliest course date per group id?
So for the sample data I have given,I need to get course4 for the first row for the groupid 1 ,course1 for the second row for that group and course2 as the third row value.
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (1,1,'course1','1/3/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (1,0,'course2','1/5/2010')
INSERT INTO [SFDC60].[dbo].[Test] (GroupID,[Rank],[coursename],[startdate]) VALUES (1,1,'course4','1/1/2010'
When I tried min() with group by GroupID and startdate, its giving the wrong answer, like its just replacing the same coursename to each row, its not checking the startdate.
Am I doing something wrong?
Hope you got me.
Thanks
January 31, 2010 at 9:57 pm
Biz (1/31/2010)
When I tried min() with group by GroupID and startdate, its giving the wrong answer, like its just replacing the same coursename to each row, its not checking the startdate.
Well, yes. Min means take the minimum value of this column per group. Nothing more. So min (startdate) will give the minimum startdate.
Does this produce (as a select) what you want?
SELECT t.GroupID, t.CourseName, t.StartDate
FROM Test t
INNER JOIN (SELECT GroupID, Min(StartDate) AS MinStartDate FROM Test Group By GroupID) sub ON sub.GroupID = t.GroupID AND sub.MinStartDate =t.StartDate
p.s please, please, please, please give the sample data WITHOUT the database name. It just makes extra work for me to trim the DB name off so that I can get the inserts to work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2010 at 10:31 pm
Sorry for the disturbing data.. I will take care...
I want to update another field with this data on the same table.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply