|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Grouping,Paging LogicCan any one give me the optimized Paging and Grouping Logic in SQL Server, my
Grouping shoul like the below.... Group By Filed Name = Group By Filed Value Group count: 2 --------------------------------------------------------------- Row ----------- 1 Row ----------- 2 .. .. --------------------------------------------------------------- Group count: 2 records Is there any direct query to get the out put in this format? SELECT
ISNULL( CAST( GroupID AS VARCHAR(11)) , 'Total' ) AS GroupID , ISNULL( CAST( SubGroupID AS VARCHAR(11)) , 'Total' ) AS SubGroupID , SUM( Value ) FROM ( SELECT TOP 0 NULL AS ID , NULL AS GroupID , NULL AS SubGroupID , NULL AS Value UNION ALL SELECT 1 , 1 , 1 , 10 UNION ALL SELECT 2 , 1 , 1 , 20 UNION ALL SELECT 3 , 1 , 2 , 30 UNION ALL SELECT 4 , 1 , 2 , 40 UNION ALL SELECT 5 , 2 , 3 , 50 UNION ALL SELECT 6 , 2 , 3 , 60 UNION ALL SELECT 7 , 2 , 4 , 70 UNION ALL SELECT 8 , 2 , 4 , 80 UNION ALL SELECT 9 , 2 , 5 , 90 ) Data GROUP BY GroupID , SubGroupID WITH ROLLUP Show quote "xslspy" <xsl***@discussions.microsoft.com> wrote in message news:FC57CBC2-6788-4BF4-BAAA-E6B040E3C76B@microsoft.com... > Can any one give me the optimized Paging and Grouping Logic in SQL Server, my > Grouping shoul like the below.... > > Group By Filed Name = Group By Filed Value Group count: 2 > --------------------------------------------------------------- > Row ----------- 1 > Row ----------- 2 > . > . > --------------------------------------------------------------- > Group count: 2 records > > Is there any direct query to get the out put in this format? > Thank you for the response , but the result is not the same as i mentioned...
result should be something like this..... (Header and footer both we need) Group Count Rows Rows Rows Group Count Show quote "Rebecca York" wrote: > > SELECT > ISNULL( CAST( GroupID AS VARCHAR(11)) , 'Total' ) AS GroupID > , ISNULL( CAST( SubGroupID AS VARCHAR(11)) , 'Total' ) AS SubGroupID > , SUM( Value ) > FROM > ( > SELECT TOP 0 NULL AS ID , NULL AS GroupID , NULL AS SubGroupID , NULL > AS Value > UNION ALL SELECT 1 , 1 , 1 , 10 > UNION ALL SELECT 2 , 1 , 1 , 20 > UNION ALL SELECT 3 , 1 , 2 , 30 > UNION ALL SELECT 4 , 1 , 2 , 40 > UNION ALL SELECT 5 , 2 , 3 , 50 > UNION ALL SELECT 6 , 2 , 3 , 60 > UNION ALL SELECT 7 , 2 , 4 , 70 > UNION ALL SELECT 8 , 2 , 4 , 80 > UNION ALL SELECT 9 , 2 , 5 , 90 > ) Data > GROUP BY > GroupID > , SubGroupID > WITH ROLLUP > > > > > > > "xslspy" <xsl***@discussions.microsoft.com> wrote in message > news:FC57CBC2-6788-4BF4-BAAA-E6B040E3C76B@microsoft.com... > > Can any one give me the optimized Paging and Grouping Logic in SQL Server, > my > > Grouping shoul like the below.... > > > > Group By Filed Name = Group By Filed Value Group count: 2 > > --------------------------------------------------------------- > > Row ----------- 1 > > Row ----------- 2 > > . > > . > > --------------------------------------------------------------- > > Group count: 2 records > > > > Is there any direct query to get the out put in this format? > > > > > Take a look at the CUBE / ROLLUP topic in Books Online. ROLLUP will
group and subtotal your data for you. -- David Portas SQL Server MVP -- If you need the Totals before the data add an order by (below)
Show quote "Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message ORDER BYnews:4357c0a9$0$142$7b0f0fd3@mistral.news.newnet.co.uk... > > SELECT > ISNULL( CAST( GroupID AS VARCHAR(11)) , 'Total' ) AS GroupID > , ISNULL( CAST( SubGroupID AS VARCHAR(11)) , 'Total' ) AS SubGroupID > , SUM( Value ) > FROM > ( > SELECT TOP 0 NULL AS ID , NULL AS GroupID , NULL AS SubGroupID , NULL > AS Value > UNION ALL SELECT 1 , 1 , 1 , 10 > UNION ALL SELECT 2 , 1 , 1 , 20 > UNION ALL SELECT 3 , 1 , 2 , 30 > UNION ALL SELECT 4 , 1 , 2 , 40 > UNION ALL SELECT 5 , 2 , 3 , 50 > UNION ALL SELECT 6 , 2 , 3 , 60 > UNION ALL SELECT 7 , 2 , 4 , 70 > UNION ALL SELECT 8 , 2 , 4 , 80 > UNION ALL SELECT 9 , 2 , 5 , 90 > ) Data > GROUP BY > GroupID > , SubGroupID > WITH ROLLUP (CASE WHEN CAST( GroupID AS VARCHAR(11)) IS NULL THEN 0 ELSE 1 END) , GroupID , (CASE WHEN CAST( SubGroupID AS VARCHAR(11)) IS NULL THEN 0 ELSE 1 END) The best way to specify a problem is to post DDL and sample data:
http://www.aspfaq.com/etiquette.asp?id=5006 You'll find you'll get useful answers faster that way. -- David Portas SQL Server MVP -- I tried CUBE & ROLLUP but its not helpful
Show quote "David Portas" wrote: > The best way to specify a problem is to post DDL and sample data: > http://www.aspfaq.com/etiquette.asp?id=5006 > > You'll find you'll get useful answers faster that way. > > -- > David Portas > SQL Server MVP > -- > > That reply isn't helpful either if you expect any more assistance. In
what way doesn't ROLLUP meet your requirements if you combine it with some form of paging? What did you try and what was the result? Is there any chance of you posting DDL and sample data so that people don't have to keep making wild guesses at your requirements? -- David Portas SQL Server MVP -- GEEEESH! What is the MOST FUNDAMENTAL principle of any tiered
architecture ?? Display and formatting is done in the front end and NEVER done in the database. When you post this on a Newsgroup, you will get a ton of proprietary kludges to help you be a bad SQL programmer. So with that statement i'd read you would design an architecture that
returned 1million rows back to the middle tier or front end only to get the first page of 10 rows. That highlights a lot about your thinking and just how useless some of it really is. Go back to basics, you need tution in the basics of efficient client tier design. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1129861478.782962.260560@g47g2000cwa.googlegroups.com... > GEEEESH! What is the MOST FUNDAMENTAL principle of any tiered > architecture ?? > > Display and formatting is done in the front end and NEVER done in the > database. > > When you post this on a Newsgroup, you will get a ton of proprietary > kludges to help you be a bad SQL programmer. > There's a difference between
Display and formatting and Returning the correct data, in the right amounts, and unduplicated to the client, which is what Celko meant. xmlspy seamingly wants to duplicate the rollup, which is redundant and pointless. Show quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:e3bh7tf1FHA.4032@TK2MSFTNGP15.phx.gbl... > So with that statement i'd read you would design an architecture that > returned 1million rows back to the middle tier or front end only to get the > first page of 10 rows. > > That highlights a lot about your thinking and just how useless some of it > really is. > > Go back to basics, you need tution in the basics of efficient client tier > design. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > news:1129861478.782962.260560@g47g2000cwa.googlegroups.com... > > GEEEESH! What is the MOST FUNDAMENTAL principle of any tiered > > architecture ?? > > > > Display and formatting is done in the front end and NEVER done in the > > database. > > > > When you post this on a Newsgroup, you will get a ton of proprietary > > kludges to help you be a bad SQL programmer. > > > > Hang on a minute, so you advocate sending 1 million rows back to the middle
tier/client front end in order to just get the first 10? So, you design applications that pass back 500MB of data (the million rows) when you only need the first 10K! Its not what Celko mean't, he's very fixed in his view that the database should pass out the 'set' of data and the middle tier/client app deal with the UI. How would you get round the problem with sending a million rows back to the client and not just the 10? I'm interested to here. Show quote "Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message news:4358a068$0$140$7b0f0fd3@mistral.news.newnet.co.uk... > There's a difference between > > Display and formatting > > and > > Returning the correct data, in the right amounts, and unduplicated to the > client, which is what Celko meant. > > xmlspy seamingly wants to duplicate the rollup, which is redundant and > pointless. > > > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > news:e3bh7tf1FHA.4032@TK2MSFTNGP15.phx.gbl... >> So with that statement i'd read you would design an architecture that >> returned 1million rows back to the middle tier or front end only to get > the >> first page of 10 rows. >> >> That highlights a lot about your thinking and just how useless some of it >> really is. >> >> Go back to basics, you need tution in the basics of efficient client tier >> design. >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlserverfaq.com - free video tutorials >> >> >> "--CELKO--" <jcelko***@earthlink.net> wrote in message >> news:1129861478.782962.260560@g47g2000cwa.googlegroups.com... >> > GEEEESH! What is the MOST FUNDAMENTAL principle of any tiered >> > architecture ?? >> > >> > Display and formatting is done in the front end and NEVER done in the >> > database. >> > >> > When you post this on a Newsgroup, you will get a ton of proprietary >> > kludges to help you be a bad SQL programmer. >> > >> >> > > No, I advocate sending the only *required* data to the middle tier/client -
which I said in my previous post. It'd be pretty damn stupid to send a million records to the client for it to do a group by and rollup. Show quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:uOn0QU71FHA.460@TK2MSFTNGP15.phx.gbl... > Hang on a minute, so you advocate sending 1 million rows back to the middle > tier/client front end in order to just get the first 10? > > So, you design applications that pass back 500MB of data (the million rows) > when you only need the first 10K! > > Its not what Celko mean't, he's very fixed in his view that the database > should pass out the 'set' of data and the middle tier/client app deal with > the UI. > > How would you get round the problem with sending a million rows back to the > client and not just the 10? I'm interested to here. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message > news:4358a068$0$140$7b0f0fd3@mistral.news.newnet.co.uk... > > There's a difference between > > > > Display and formatting > > > > and > > > > Returning the correct data, in the right amounts, and unduplicated to the > > client, which is what Celko meant. > > > > xmlspy seamingly wants to duplicate the rollup, which is redundant and > > pointless. > > > > > > > > "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message > > news:e3bh7tf1FHA.4032@TK2MSFTNGP15.phx.gbl... > >> So with that statement i'd read you would design an architecture that > >> returned 1million rows back to the middle tier or front end only to get > > the > >> first page of 10 rows. > >> > >> That highlights a lot about your thinking and just how useless some of it > >> really is. > >> > >> Go back to basics, you need tution in the basics of efficient client tier > >> design. > >> > >> -- > >> Tony Rogerson > >> SQL Server MVP > >> http://sqlserverfaq.com - free video tutorials > >> > >> > >> "--CELKO--" <jcelko***@earthlink.net> wrote in message > >> news:1129861478.782962.260560@g47g2000cwa.googlegroups.com... > >> > GEEEESH! What is the MOST FUNDAMENTAL principle of any tiered > >> > architecture ?? > >> > > >> > Display and formatting is done in the front end and NEVER done in the > >> > database. > >> > > >> > When you post this on a Newsgroup, you will get a ton of proprietary > >> > kludges to help you be a bad SQL programmer. > >> > > >> > >> > > > > > > I have to agree with Celko,
Get the data out and put it above and below the results in the front end. There's no need to duplicate the data in the resultset. Show quote "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1129861478.782962.260560@g47g2000cwa.googlegroups.com... > GEEEESH! What is the MOST FUNDAMENTAL principle of any tiered > architecture ?? > > Display and formatting is done in the front end and NEVER done in the > database. > > When you post this on a Newsgroup, you will get a ton of proprietary > kludges to help you be a bad SQL programmer. > But we neeed consider the requirements too,my requirement is i need disply
the rows groupby one column but the page size should not go beyond 50 rows, and the problem here is if we are getting 50 rows to the client and using client side group by (may be we can use xsl etc., for this) then it only apply the group by to those 50 records what if i have 100 rows in that group and want to dispaly only 50 rows at a time but the total should show as 100.i think these kind of scenarios we need have some processing logic at back end , i wanted to collect the better approaches , i think it explains if any one like to help me out in this i can send the sample code procedure code and result set and the xsl i am using. Show quote "Rebecca York" wrote: > I have to agree with Celko, > > Get the data out and put it above and below the results in the front end. > > There's no need to duplicate the data in the resultset. > > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > news:1129861478.782962.260560@g47g2000cwa.googlegroups.com... > > GEEEESH! What is the MOST FUNDAMENTAL principle of any tiered > > architecture ?? > > > > Display and formatting is done in the front end and NEVER done in the > > database. > > > > When you post this on a Newsgroup, you will get a ton of proprietary > > kludges to help you be a bad SQL programmer. > > > > > You'll have to sum them on the client, or use paging:
see: http://www.aspfaq.com/show.asp?id=2120 Show quote "xslspy" <xsl***@discussions.microsoft.com> wrote in message news:85D2E6AB-B1D2-4811-979E-2DA29FA983A4@microsoft.com... > But we neeed consider the requirements too,my requirement is i need disply > the rows groupby one column but the page size should not go beyond 50 rows, > and the problem here is if we are getting 50 rows to the client and using > client side group by (may be we can use xsl etc., for this) then it only > apply the group by to those 50 records what if i have 100 rows in that group > and want to dispaly only 50 rows at a time but the total should show as 100.i > think these kind of scenarios we need have some processing logic at back end > , i wanted to collect the better approaches , i think it explains if any one > like to help me out in this i can send the sample code procedure code and > result set and the xsl i am using. > > "Rebecca York" wrote: > > > I have to agree with Celko, > > > > Get the data out and put it above and below the results in the front end. > > > > There's no need to duplicate the data in the resultset. > > > > > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > > news:1129861478.782962.260560@g47g2000cwa.googlegroups.com... > > > GEEEESH! What is the MOST FUNDAMENTAL principle of any tiered > > > architecture ?? > > > > > > Display and formatting is done in the front end and NEVER done in the > > > database. > > > > > > When you post this on a Newsgroup, you will get a ton of proprietary > > > kludges to help you be a bad SQL programmer. > > > > > > > > > Yes i can sum them at client but if i want to sum for the total 100 records ?
this can not be done at client because at client we have only 50 records , as we are throwing out 50 records per page, do i need to get one more dataset with all the groups and their totals? and then display? Show quote "Rebecca York" wrote: > You'll have to sum them on the client, or use paging: > > see: http://www.aspfaq.com/show.asp?id=2120 > > > "xslspy" <xsl***@discussions.microsoft.com> wrote in message > news:85D2E6AB-B1D2-4811-979E-2DA29FA983A4@microsoft.com... > > But we neeed consider the requirements too,my requirement is i need disply > > the rows groupby one column but the page size should not go beyond 50 > rows, > > and the problem here is if we are getting 50 rows to the client and using > > client side group by (may be we can use xsl etc., for this) then it only > > apply the group by to those 50 records what if i have 100 rows in that > group > > and want to dispaly only 50 rows at a time but the total should show as > 100.i > > think these kind of scenarios we need have some processing logic at back > end > > , i wanted to collect the better approaches , i think it explains if any > one > > like to help me out in this i can send the sample code procedure code and > > result set and the xsl i am using. > > > > "Rebecca York" wrote: > > > > > I have to agree with Celko, > > > > > > Get the data out and put it above and below the results in the front > end. > > > > > > There's no need to duplicate the data in the resultset. > > > > > > > > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > > > news:1129861478.782962.260560@g47g2000cwa.googlegroups.com... > > > > GEEEESH! What is the MOST FUNDAMENTAL principle of any tiered > > > > architecture ?? > > > > > > > > Display and formatting is done in the front end and NEVER done in the > > > > database. > > > > > > > > When you post this on a Newsgroup, you will get a ton of proprietary > > > > kludges to help you be a bad SQL programmer. > > > > > > > > > > > > > > > > Write a stored proc which returns two recordsets (ADO can handle multiple
recordsets IIRC) First recordset: Rollups of 100 records 2nd Recordset: paged 50 records that you're interested in. Show quote "xslspy" <xsl***@discussions.microsoft.com> wrote in message news:0DA3BD7F-B4C4-4DD4-B656-0E02A1ABDD2B@microsoft.com... > Yes i can sum them at client but if i want to sum for the total 100 records ? > this can not be done at client because at client we have only 50 records , as > we are throwing out 50 records per page, do i need to get one more dataset > with all the groups and their totals? and then display? > > "Rebecca York" wrote: > > > You'll have to sum them on the client, or use paging: > > > > see: http://www.aspfaq.com/show.asp?id=2120 > > > > > > "xslspy" <xsl***@discussions.microsoft.com> wrote in message > > news:85D2E6AB-B1D2-4811-979E-2DA29FA983A4@microsoft.com... > > > But we neeed consider the requirements too,my requirement is i need disply > > > the rows groupby one column but the page size should not go beyond 50 > > rows, > > > and the problem here is if we are getting 50 rows to the client and using > > > client side group by (may be we can use xsl etc., for this) then it only > > > apply the group by to those 50 records what if i have 100 rows in that > > group > > > and want to dispaly only 50 rows at a time but the total should show as > > 100.i > > > think these kind of scenarios we need have some processing logic at back > > end > > > , i wanted to collect the better approaches , i think it explains if any > > one > > > like to help me out in this i can send the sample code procedure code and > > > result set and the xsl i am using. > > > > > > "Rebecca York" wrote: > > > > > > > I have to agree with Celko, > > > > > > > > Get the data out and put it above and below the results in the front > > end. > > > > > > > > There's no need to duplicate the data in the resultset. > > > > > > > > > > > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > > > > news:1129861478.782962.260560@g47g2000cwa.googlegroups.com... > > > > > GEEEESH! What is the MOST FUNDAMENTAL principle of any tiered > > > > > architecture ?? > > > > > > > > > > Display and formatting is done in the front end and NEVER done in the > > > > > database. > > > > > > > > > > When you post this on a Newsgroup, you will get a ton of proprietary > > > > > kludges to help you be a bad SQL programmer. > > > > > > > > > > > > > > > > > > > > > > > Thanks all for your excellent support in bringing down the issue, i am
implementing the Rebecca suggestions.. I designed stored procedure which returns two resultsets one with 50 rows and the other with the Group totals and displaying it. Once again thank you very much. Show quote "Rebecca York" wrote: > Write a stored proc which returns two recordsets (ADO can handle multiple > recordsets IIRC) > > First recordset: Rollups of 100 records > 2nd Recordset: paged 50 records that you're interested in. > > > "xslspy" <xsl***@discussions.microsoft.com> wrote in message > news:0DA3BD7F-B4C4-4DD4-B656-0E02A1ABDD2B@microsoft.com... > > Yes i can sum them at client but if i want to sum for the total 100 > records ? > > this can not be done at client because at client we have only 50 records , > as > > we are throwing out 50 records per page, do i need to get one more dataset > > with all the groups and their totals? and then display? > > > > "Rebecca York" wrote: > > > > > You'll have to sum them on the client, or use paging: > > > > > > see: http://www.aspfaq.com/show.asp?id=2120 > > > > > > > > > "xslspy" <xsl***@discussions.microsoft.com> wrote in message > > > news:85D2E6AB-B1D2-4811-979E-2DA29FA983A4@microsoft.com... > > > > But we neeed consider the requirements too,my requirement is i need > disply > > > > the rows groupby one column but the page size should not go beyond 50 > > > rows, > > > > and the problem here is if we are getting 50 rows to the client and > using > > > > client side group by (may be we can use xsl etc., for this) then it > only > > > > apply the group by to those 50 records what if i have 100 rows in that > > > group > > > > and want to dispaly only 50 rows at a time but the total should show > as > > > 100.i > > > > think these kind of scenarios we need have some processing logic at > back > > > end > > > > , i wanted to collect the better approaches , i think it explains if > any > > > one > > > > like to help me out in this i can send the sample code procedure code > and > > > > result set and the xsl i am using. > > > > > > > > "Rebecca York" wrote: > > > > > > > > > I have to agree with Celko, > > > > > > > > > > Get the data out and put it above and below the results in the front > > > end. > > > > > > > > > > There's no need to duplicate the data in the resultset. > > > > > > > > > > > > > > > "--CELKO--" <jcelko***@earthlink.net> wrote in message > > > > > news:1129861478.782962.260560@g47g2000cwa.googlegroups.com... > > > > > > GEEEESH! What is the MOST FUNDAMENTAL principle of any tiered > > > > > > architecture ?? > > > > > > > > > > > > Display and formatting is done in the front end and NEVER done in > the > > > > > > database. > > > > > > > > > > > > When you post this on a Newsgroup, you will get a ton of > proprietary > > > > > > kludges to help you be a bad SQL programmer. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics