tag:blogger.com,1999:blog-495177919198719500.post7141502676620527667..comments2023-12-27T20:52:29.483-08:00Comments on Microsoft Sql Server Tutorials: SELECT TOP N Rows Per Group/CategorySuprotim Agarwalhttp://www.blogger.com/profile/08349831623922214390noreply@blogger.comBlogger19125tag:blogger.com,1999:blog-495177919198719500.post-62944050311333881972012-02-22T07:07:28.753-08:002012-02-22T07:07:28.753-08:00I have a History Table tbl_history which contains ...I have a History Table tbl_history which contains crores of Records.<br /> <br />Table Sample Data:-<br />id Volume DeletedTime <br />1 C 11am <br />1 C 12am <br />1 C 1pm <br />1 C 2pm <br />1 C 3pm <br />1 D 11am <br />1 D 12am <br />1 D 1pm <br />1 D 2pm <br />1 D 3pm <br />2 C 11am <br />2 C 12am <br />2 C 1pm <br />2 C 2pm <br />2 C 3pm <br />2 D 11am <br />2 D 12am <br />2 D 1pm <br />2 D 2pm <br />2 D 3pm <br /><br />Now what i want is to list out last 3 Records per ID and per Category i.e in our Case Volume, without using Row number function the reason i am looking to implement some other logic other than Row_number() because query takes long time to execute.GirishChhatanihttps://www.blogger.com/profile/13013760442577996555noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-16389186348309813202012-02-22T03:50:39.343-08:002012-02-22T03:50:39.343-08:00is it possible without using Row number function t...is it possible without using Row number function the reason i am looking to implement some other logic bcse Row_number() query which i am firing on a History table which contains lakhs of records... so query takes long time to execute.<br /><br />So pls help me out...<br />Thanks & Regards,<br />Girish ChhataniGirishChhatanihttps://www.blogger.com/profile/13013760442577996555noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-7830055030371219852011-12-25T13:26:19.168-08:002011-12-25T13:26:19.168-08:00After many hours searching and trying 'solutio...After many hours searching and trying 'solutions', I finally managed to find something that actually works and I can easily understand.<br /><br />Thanks! :)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-67443776584099920562011-08-12T13:44:56.028-07:002011-08-12T13:44:56.028-07:00This is a great, clear, and to the point post. Tha...This is a great, clear, and to the point post. Thanks!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-17066526828273593162011-02-03T09:22:18.888-08:002011-02-03T09:22:18.888-08:00Cheers for that ... I got just what I needed after...Cheers for that ... I got just what I needed after looking for ages.odanyboynoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-64772383714279102912011-02-02T03:31:59.668-08:002011-02-02T03:31:59.668-08:00Fantastic, thank you...JUST what I needed.Fantastic, thank you...JUST what I needed.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-91597452585478174172010-10-13T14:25:05.751-07:002010-10-13T14:25:05.751-07:00GENIUS! exactly what I needed and worked perfectly...GENIUS! exactly what I needed and worked perfectly! THANK YOU!!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-29713816311347284162010-06-02T01:27:58.381-07:002010-06-02T01:27:58.381-07:00realised neeeded to place a select into the last p...realised neeeded to place a select into the last part...<br /><br />WITH CTE AS <br />(<br />SELECT ROW_NUMBER() OVER (PARTITION BY locid ORDER BY loccvgid) AS 'RowNo',<br />loccvgid, locid<br />FROM loccvg<br />)<br />SELECT <br />LOCCVGID, locid<br />into #t<br />FROM CTE<br />WHERE RowNo <= 1Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-28574860930353519412010-06-02T01:04:12.865-07:002010-06-02T01:04:12.865-07:00if i want to get the results post the query and pl...if i want to get the results post the query and place into a #tempTable, how would i append the script?raxnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-69220579034474962822010-03-25T08:37:38.605-07:002010-03-25T08:37:38.605-07:00Great post, thanks. I added a variation that allo...Great post, thanks. I added a variation that allows you to sort the CTE query. Here, I sorted by "Priority"<br /><br />WITH CTE AS <br />(<br /> SELECT top 250000 ROW_NUMBER() OVER (PARTITION BY CompanyName ORDER BY ID) AS 'RowNo', <br /> ID, CompanyName, Email, Priority<br /> FROM ABCTABLE<br /> order by priority desc<br />)<br /><br />Delete from ABCTABLE where email in (<br />SELECT Email<br />FROM CTE<br />WHERE RowNo > 5)<br /><br /><br />... This let me take a company where we have multiple contacts and narrow down to the top 5, by priority.<br /><br />Note: The TOP 250000 - to do the ORDER BY you need a TOP parameter. I picked 250,000 because the table had 175,000 records.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-73147049610361680212010-02-03T06:03:42.923-08:002010-02-03T06:03:42.923-08:00Here are some other methods
http://beyondrelationa...Here are some other methods<br />http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx<br /><br />MadhivananUnknownhttps://www.blogger.com/profile/13241916831072779717noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-49836860018502721722009-12-14T06:59:12.368-08:002009-12-14T06:59:12.368-08:00Good job with this.Good job with this.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-3663876063050868322009-11-15T22:22:58.016-08:002009-11-15T22:22:58.016-08:00Thank you so much! I'm working on a project fo...Thank you so much! I'm working on a project for school and this was so helpful. Great explanation as I just could not wrap my head around some of the other examples I had found!Unknownhttps://www.blogger.com/profile/17857210968619505366noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-67246557708771535762009-04-01T12:48:00.000-07:002009-04-01T12:48:00.000-07:00very useful! thanx!very useful! thanx!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-82257217510664175902009-03-03T10:17:00.000-08:002009-03-03T10:17:00.000-08:00Thanks Its so nice, I dindt know about all this, g...Thanks <BR/>Its so nice, I dindt know about all this,<BR/> good oneAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-87182602706292878142009-02-11T16:49:00.000-08:002009-02-11T16:49:00.000-08:00This is EXACTLY the kind of technique I have been ...This is EXACTLY the kind of technique I have been looking for. Thank you very much for sharing it!phaedrushttps://www.blogger.com/profile/02626367155332936845noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-67528505051837669992009-02-10T18:13:00.000-08:002009-02-10T18:13:00.000-08:00I am Glad it helped you out!I am Glad it helped you out!Suprotim Agarwalhttps://www.blogger.com/profile/08349831623922214390noreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-42090546612571188012009-02-09T12:59:00.000-08:002009-02-09T12:59:00.000-08:00This was the most lucid, clear explanation of this...This was the most lucid, clear explanation of this process. Worked like a charm, and save me hours (and probably a few torn-out hairs).<BR/><BR/>Thank.sAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-495177919198719500.post-83736858583243270242008-10-19T17:45:00.000-07:002008-10-19T17:45:00.000-07:00PARTITION BY was the key I was looking for. Thank...PARTITION BY was the key I was looking for. Thanks!Anonymousnoreply@blogger.com