.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links


Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server

Hi all

I have the following table:

ID IsTop Value
1 1 TestGroup1
2 0 Child 1 (TestGroup1)
3 0 Child 2 (TestGroup1)
4 1 TestGroup2
5 0 Child 1 (TestGroup2)
6 0 Child 2 (TestGroup2)
7 0 Child 3 (TestGroup2)

Now I need to find out the groups,
The ID 1, 2, 3 are one Group
and the IDs 4, 5, 6, 7 are another Group.
The group names are not in the value,
it is just for this sample to show what I mean.

To identify I need to get IsTop = 1 and all following
rows until IsTop again = 1.

I have tried to use it with PARTITION BY, but this does not work.
Any other ideas ?

Best regards
Frank Uray

P.S. Here are the script for testing:

CREATE TABLE #tempPARTITION ([ID] int, [IsTop] int, [Value] varchar(255))
INSERT INTO #tempPARTITION ([ID], [IsTop], [Value])
SELECT 1, 1, 'TestGroup1'
SELECT 2, 0, 'Child 1 (TestGroup1)'
SELECT 3, 0, 'Child 2 (TestGroup1)'
SELECT 4, 1, 'TestGroup2'
SELECT 5, 0, 'Child 1 (TestGroup2)'
SELECT 6, 0, 'Child 2 (TestGroup2)'
SELECT 7, 0, 'Child 3 (TestGroup2)'


View Complete Post

More Related Resource Links

ROW_NUMBER() OVER - PARTITION BY should be different from ORDER BY

I have noticed incorrect usage for OVER - PARTITION BY - ORDER BY with same (BY) column lists, making the query nondeterministic as far as the generated ROW_NUMBER() is concerned.  If random sort needed, use NEWID() instead. Demo follows. -- WRONG PARTITION BY ... ORDER BY ... usage - nondeterministic sort SELECT ProductName = Name, Color, PartRowID = ROW_NUMBER() OVER(PARTITION BY Color ORDER BY Color) FROM AdventureWorks2008.Production.Product WHERE Color IS NOT NULL ORDER BY Color, PartRowID /* ProductName Color PartRowID .... Men's Bib-Shorts, L Multi 3 AWC Logo Cap Multi 4 Long-Sleeve Logo Jersey, S Multi 5 .... */ -- CORRECT PARTITION BY ... ORDER BY ... usage - UNIQUE sort (Name is unique) SELECT ProductName = Name, Color, PartRowID = ROW_NUMBER() OVER(PARTITION BY Color ORDER BY Name) FROM AdventureWorks2008.Production.Product WHERE Color IS NOT NULL ORDER BY Color, PartRowID /* ProductName Color PartRowID .... Long-Sleeve Logo Jersey, M Multi 3 Long-Sleeve Logo Jersey, S Multi 4 Long-Sleeve Logo Jersey, XL Multi 5 Men's Bib-Shorts, L Multi 6 .... */ Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

replacing Group By with row_number(partition by ...)

I have an indexed view and I query this view to get counts on how many products each seller has in a specific category. This query works perfect and produces results in 45ms. I want to join additional tables and apply additional WHERE filters to the result set. I am not able to do it because I had to get products_pid out of With clause to be able to join other tables after CTE. I couldn't add products_pid to the select list because it would require me to have in the Group By. After reading articles in this forum, I found out that Group By can be replaced by row_number() over (partition by...). I re-wrote my query using row_number(). But this query takes 1100 ms to execute. Is this the write way to get rid of Group By? What am I doing wrong here and why it takes so long to execute?
Thank you,

Original query:
set statistics time on
with keys as (
select getproductssellers.productslocation_cid,
count(distinct getproductssellers.products_pid) as CNT
from GetProductsSellers WITH (NOEXPAND)
WHERE getproductssellers.products_subcategory=26
group by getproductssellers.productslocation_cid
select customer.businessname as Seller, productslocation_cid, CNT
from keys
inner join custom

Partition Query

Hi All, I have cube with 10 partition ,i want to process only last partition with with last 30 days . Now i want to know when i processed this partition of 30 days whether data of 30 days updated or get added with the existing one ...if getdate-1 havunf value 10 in column a before 30 days partition will become 20 after i process only 30 days partition alone   Kindly suggest Amit

how to change a remote partition into local partition

Our production cube is very huge and the process time is very long (10+ hours), and the data volume is still increasing. Since we have some idle server during cube process period, I'd like to try to put some partitions on the idle server to cut the process time. But I also want to bring these remote partitions back to local after the process is completed. Is it possible to change partition from remote to local? if so, how?

Confused about Disk Alignment/ Partition Offset

I am all confused by disk alignment, partiton offset.Why do we need to calcualte offset which most of the articles talk about, This is from White Paper  Disk Partition Alignment Best Practices for SQL Server where its stressed that Partition_Offset ÷ Stripe_Unit_Size-- should be an integer 'The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server.' Also how do we calculate Stripe Unit Size for SAN. Thanks all for your replies !!  

IS There is any way out to process only one partition of cube and data of other partition remain ava

HI ALL. Please let me know is there is any way out to process one partition only,while data of other partition remain available in the cube with the help of SSIS

sql server 2008 Table Partition

HI i want to implement partitioning in my sql server 2008 database table http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx   i have used this link. but when i try this link then i am able to create only primary file group no secondary file group..so help me also by fault i have created partition in wrong table .so i want to detete tha partition from that table but of i delete the PARTITION SCHEME and PARTITION FUNCTION THEN it throws error..so what shoul i do to remove partition from my current table

sql server 2008 Table Partition

HI http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx  i have used  above link to create partition,but my problem is i want to do partition on daily basic, one column in my table is date(by default has getdate()),on the base of this i want to do partition, eg.in this link i need to give time duration between which one can do partition, and also either daily or monthly or quarterly or yearly need to be specify ..if i give date between 01/08/2010 to 01/08/2012 and then select daily wise partition which i am in need then table create more than 700 partion but problem comes when i need to assign group to them how cud i select Primary file group more than 700 times to assign it to each partiotion.it becomes very lengthy process  Please help me

how to process just one partition along with other measure group and dimension in SSIS package Analy

HI All, i have to process just one partition1  of measure group A ,along with this i suppose to process all the Measure group and dimension with the help of SSIS Package Analysis Services Processing task. Partition1 having a query which fetch data only for previous day only. what i have done i select partition 1 in process data mode,all other measure group in full mode and dimension in process update mode.   i haven't taken measure group of partition1 and also not taken cube in the processing list ,when i run the package ,it runs suceesfully but data not get uplaoded into the Cube.   kindly suggest what other measures should i take to update the data . Amit

Unprocess cube old partition

hi all, i have a cube,and i wanna unprocess old cube partition(last 2 years)how can i do that ?

Partition sql server 2008

I am designing database for fleet management system. I will be getting n number of records every 3 seconds. Obviously, there will be millions of record in my table where I am going to store current Information of vehicle in the current_location table. Here performance is an BIG issue. To solve this, I received the following suggestions: Create an septate table for each vehicle. Here a table will be created at a run time as as soon as I click on create new table.And all the data related to perticular table will be inserted and retrive from that perticular table Go for partition. Please answer the following questions about these solutions. What is difference between the two? Which is best and why? At what point will the number of rows in the tables cause performance issues? Are there any other solutions? Now ---if I go for range partition in sql server 2008 what should i do to, partition useing varchar(20). i am planning to do partition based on vehicle no.eg MH30 q 1234. Here In vehicle no. lets say mh30 q 1234--only 30 & q going to change....so my question is HOW SHOULD I GO. means how should write the partition function.

processing a particular partition dynamically using the AS Processing Task and Property Expressions

HI ,  Do any one have example/screenshots/article  of "processing  a particular partition dynamically using the AS Processing Task and Property Expressions..."   I have 4 reports  and 12 partitions for the report one it uses 1-4 partitions for report2 it uses 5-8 partitions,, is there any way to process the partitions dynamically , if i say report 1 it should process 1-4 partitions, report 4 it should process 9-12 partitions,     Thanks

Data missed on processing Partition

HI, I have created partition in two partition in SSAS cube, one contain data of >=getdate()-10 to <getdate()-2 while other conains >=getdate()-2 to <getdate(),,,,[[getdate() converted to date part only ]] i have created one SSIS package in which i have used "Analysis Service Processing task" and processed that partition whcih contaons >=gedate()-2 to <getdate() in Full Process mode. but i have got error which i explained you below. supoose on 20 Aug,i have data from 11 Aug to 18 aug and when i process the SSIS package ,it runs fine and update 19 August data but also eliminate the data of 17 August.   Kindly let me know if there is some mistake comiited by me or suggest.   Regards Shraddha  

problem with row_number()

Hi All,am using sql server 2005am using the row_number with the query using the joins.Its not workingits giving the error as Windowed functions can only appear in the SELECT or ORDER BY clauses.the query is : select  * from (select C.company_id,c.company_name,count(P.people_id)contacts,C.city,C.state,C.country,C.industry from Company As C Left Join people As P On C.company_id=P.company_id where C.company_name like 'ra%' and C.verify_status='Yes' and C.delete_status='No' group by C.company_id,c.company_name,C.city,C.state,C.country,C.industry,row_number() over (order by c.company_name desc)) C WHERE C.RowNum BETWEEN 0 AND 10please suggest me th solution for this,Thanks in Advance,NIkhil

processing a particular partition dynamically using the AS Processing Task and Property Expressions

HI ,  Does any one have example/screenshots/article  of "processing  a particular partition dynamically using the AS Processing Task and Property Expressions..."     Thanks

Requested Conversion is not supported, error while processing the partition

I have read the other topics that are related this issue and I thought that my issue was related as well until I checked the datatypes of all my measures and made sure that they indeed could be inherited properly.   I even went as far as deleting all measures out the measure group and I still recieved this error.   Is there a way I get more detailed information on what is not being converted properly?  The error is so vague and again, I have thouroughly checked my datatypes and they are fine and why would the partition not process, throwing me the exact same error when I decided to take out all measures in the measure group.   Here the second strange thing, I know that it has to be data related because I also ran the process structure and that finished with success. Please let me know if there is a better way to find the problem child or problem children pertaining to my measure group when processing data Here is a last thought, do you think that it could have something to do with the datasize?  I know SSAS automatically converts the datasize and I see several in my measures that have a zero for the size, I am speculating that could be wrong but is there a way I can check the datasize by running the script on my fact table?   ThanksNetwork Analyst

Finding table data partition bounds key values using SQL

I have a table that has few million rows and I need the front end application to partition all the data for this table, or any other large table, using sql where clauses so I can retrieve all the data for the table in different threads for each "partition" in parallel (I can't/do not need to know whether the table data is actually partitioned on any specific column at the server). Assume that each "partition" can have n rows (dynamically), is there an easy way of find out what are the bounds for such partitions in terms of the table PK/unique index? Example, create table table_test (col1 char(8) primary key); Example table data: col1 (PK) a b c d e f g   The bounds for table_test for partitions of 2 rows would be four partitions that have the following values for col1: c, e, g  and the font end app would be able to use these bound to generate sql such as: select * from test_table where col1 < 'c'; -- first partition select * from test_table where col1 >= 'c' and col1 < 'e'; -- second partition select * from test_table where col1 >= 'e' and col1 < 'g'; -- third partition select * from test_table where col1 >= 'g'; -- fourth partition So the question is whether the values 'c', 'e', 'g' bounds of the table data can be easily determined by using sql by the client font end.        Farid Zidan Zi
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend