May 4, 2009 at 3:13 am
Hello,
There are reminders that belong to several reminder groups.
I need to select the nearest reminder from each group.
the query is dynamically generated from the client application,
therefore I'd like to avoid the cursors.
Sample Data:
ID groupid days_remaining
1 89 11
2 96 6
3 89 11
4 89 14
5 96 10
the result should look like this:
ID groupid days_remaining
1 89 11
2 96 6
i get the desirable rows with this query
SELECT groupid, MIN(days_remaining) FROM reminder
GROUP BY groupid
But how do I include the IDs?
May 4, 2009 at 3:22 am
According to the Forum Etiquette[/url], hier is the DDL query:
IF IsNull(object_id('tempdb..#reminder'),0) 0
DROP TABLE #reminder;
GO
CREATE TABLE #reminder
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
groupid INT,
days_remaining INT
)
GO
SET IDENTITY_INSERT #reminder ON
GO
INSERT INTO #reminder
(ID, groupid, days_remaining)
SELECT '1','89','11' UNION ALL
SELECT '2','96','6' UNION ALL
SELECT '3','89','11' UNION ALL
SELECT '4','89','14' UNION ALL
SELECT '5','96','10'
GO
SET IDENTITY_INSERT #reminder OFF
GO
May 4, 2009 at 3:34 am
Since you're on SQL 2005, use the Row_Number() function
SELECT groupid, days_remaining
FROM
(SELECT groupid, days_remaining, Row_Number() OVER (Partition By groupid order by days_remaining) AS RowNo FROM reminder) sub
WHERE RowNo = 1
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
May 4, 2009 at 3:44 am
Thanks!
May 4, 2009 at 3:46 am
How do I mark your post as "Answered"?
May 4, 2009 at 3:52 am
Hi,
Try this,
DECLARE @CountGroup INT
SELECT @CountGroup = Count(Distinct GroupID) FROM reminder
SELECT TOP (@CountGroup) id, groupid, MIN(days_remaining) FROM reminder
GROUP BY groupid, id
May 4, 2009 at 3:54 am
You don't. There's no 'answered posts' feature on this forum.
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
May 4, 2009 at 4:00 am
Sorry , i dont know about it. I was replying first time.
May 4, 2009 at 4:03 am
Hi Gail,
In 2000, how can we handle?
Doing this is better way,
Declare @abc TABLE
(
Id int,
groupid int,
days_remaining int
)
insert into @abc values (1,89,11)
insert into @abc values (2,96,6)
insert into @abc values (3,89,11)
insert into @abc values (4,89,14)
insert into @abc values (5,96,10)
SELECT IDENTITY(int, 1,1) AS id ,groupid, min(days_remaining)days_remaining into #temp FROM @abc
GROUP BY groupid
select * from #temp
ARUN SAS
May 4, 2009 at 4:11 am
Hello pushpa.kumari,
thank you for your reply, but your solution doesn't work if I change sample data as follows:
ID groupid days_remaining
1 89 16
2 96 6
3 89 14
4 89 14
5 96 10
the result should be:
ID groupid days_remaining
3 89 14
2 96 6
and yours is:
ID groupid days_remaining
1 89 16
2 96 6
the code that GilaMonster suggested works always
May 4, 2009 at 4:16 am
arun.sas (5/4/2009)
Hi Gail,In 2000, how can we handle?
Your way should work. Since this was posted in the SQL 2005 forum, I'm assuming the poster uses SQL 2005 and hence the use of the SQL 2005 features.
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
May 4, 2009 at 4:33 am
Hello arun.sas,
If changing the sample data as follows:
ID groupid days_remaining
1 89 16
2 96 6
3 89 14
4 89 14
5 96 10
the result should be:
ID groupid days_remaining
3 89 14
2 96 6
and your query returns:
ID groupid days_remaining
1 89 14
2 96 6
the ID for the nearest reminder of the group 89 is falsified
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply