SQL Server Performance

sql query result grouping by size

Discussion in 'SQL Server 2005 General Developer Questions' started by nvemuri, Jul 13, 2006.

  1. nvemuri New Member

    I have a sql query that returns 20 records. I want to group them as follows.<br />First 5 belongs to a group, Next 5 belongs to a different group and so on!!<br /><br />Any ideas <img src='/community/emoticons/emotion-6.gif' alt=':(' />
  2. spacemonkey New Member

    Will your query always return 20 rows? Are you using top 20? What happens if there are only 13 rows that would be returned by the query?

    Once that is decided, how will those rows be ordered so that order has predictability?

    And now...

    What does the return of the query look like? Do you have a sample?

    Thanks

    John
  3. nvemuri New Member

    thanks spacemonkey for those questions..
    i would say partition the total rows by 4 with a round off!
    some thing like first 25%, second 25% and so on..
  4. nvemuri New Member

    I have a column based on which i order, in descending order!!

    Keeping things simple, return results can give sum of a particular column..
  5. spacemonkey New Member

    Here is one option that is vaid in both 2000 and 2005. The problem with it is if fewer than 20 rows are returned, the totals may be off. I have not figured out a way to get the max(rank) into the query quite yet. I'm still working on it.

    select sum(id) as Totals from (select rank=count(*), a1.id from
    (select top 20 id from sysobjects order by id) a1,
    (select top 20 id from sysobjects order by id) a2
    where a1.id >= a2.id
    group by a1.id) ranktable group by ceiling(cast(rank as real)/5)

    John
  6. spacemonkey New Member

    OK I gave up on trying to do it in SQL server 2000. Since you posted this in the 2005 forums, I don't feel too bad. Since I don't have SQL 2005 at my disposal I'm assuming this is correct or at least close. You can change the basics of the inner query to select whatever you want. I just took the top 20 id values from sysobjects again and ranked them. I used (rank_asc+rank_desc-1) to get the total rows returned by the inner query so it adjusts accordingly.

    ***************************************************************

    select ceiling(cast(rank_asc as real)/((rank_asc+rank_desc-1)/4)) as GroupNumber, sum(id) as Totals
    from (select top 20 rank() OVER (ORDER BY id asc) as rank_asc, rank() OVER (ORDER BY id desc) as rank_desc, id
    from sysobjects order by id) ranktable
    group by ceiling(cast(rank_asc as real)/((rank_asc+rank_desc-1)/4)) order by GroupNumber

    ***************************************************************

    If you want to get fancy, add a where clause like this to omit any rows that do not complete a set of 4. i.e. omit the 13th row so that only the first 12 are counted.

    ***************************************************************

    select ceiling(cast(rank_asc as real)/((rank_asc+rank_desc-1)/4)) as GroupNumber, sum(id) as Totals
    from (select top 20 rank() OVER (ORDER BY id asc) as rank_asc, rank() OVER (ORDER BY id desc) as rank_desc, id
    from sysobjects order by id) ranktable
    where (rank_asc <= ((rank_asc+rank_desc-1) - ((rank_asc+rank_desc-1) % 4)))
    group by ceiling(cast(rank_asc as real)/((rank_asc+rank_desc-1)/4)) order by GroupNumber

    ***************************************************************



    John

Share This Page