Display top n records and consolidate the rest of

Question

I am relatively new to writing SQL. I have a requirement where I have to display the top 5 records as it is and consolidate the rest as 1 single record and append it as the 6th record. I know top 5 selects the top 5 records, but I am finding it difficult to put together a logic to consolidate the rest of the records and append it at the bottom of the result set.

weekof          sales    year    weekno
-------------------------------------------------------------
07/01 - 07/07   2   2012    26  
07/08 - 07/14   2   2012    27  
07/29 - 08/04   1   2012    30  
08/05 - 08/11   1   2012    31  
08/12 - 08/18   32  2012    32  
08/26 - 09/01   2   2012    34  
09/02 - 09/08   8   2012    35  
09/09 - 09/15   46  2012    36   
09/16 - 09/22   26  2012    37

---

I want this to be displayed as

weekof          sales
----------------------
09/16 - 09/22   26  
09/09 - 09/15   46  
09/02 - 09/08   8   
08/26 - 09/01   2   
08/12 - 08/18   32  
07/01 - 08/11   6

Answer 1

Here's an alternate version that displays in the correct order even when crossing years, and requires a single scan instead of two (important only if the amount of data is large). It will work in SQL 2005 and up:

WITH Sales AS (
   SELECT
      Seq = Row_Number() OVER (ORDER BY S.yr DESC, S.weekno DESC),
      *
   FROM dbo.SalesByWeek S
)
SELECT
   weekof =
      Substring(Min(Convert(varchar(11), S.yr) + Left(S.weekof, 5)), 5, 5)
      + ' - '
      + Substring(Max(Convert(varchar(11), S.yr) + Right(S.weekof, 5)), 5, 5),
   sales = Sum(S.sales)
FROM
   Sales S
   OUTER APPLY (SELECT S.Seq WHERE S.Seq <= 5) G
GROUP BY G.Seq   
ORDER BY Coalesce(G.Seq, 2147483647)
;

There's also a query possible that might perform even better for very large data sets, since it doesn't calculate row number (using TOP once instead). But this is all theoretical without real data and performance testing:

SELECT
   weekof =
      Substring(Min(Convert(varchar(11), S.yr) + Left(S.weekof, 5)), 5, 5)
      + ' - '
      + Substring(Max(Convert(varchar(11), S.yr) + Right(S.weekof, 5)), 5, 5),
   sales = Sum(S.sales)
FROM
   dbo.SalesByWeek S
   LEFT JOIN (
      SELECT TOP 5 *
      FROM dbo.SalesByWeek
      ORDER BY yr DESC, weekno DESC
   ) G ON S.yr = G.yr AND S.weekno = G.weekno
GROUP BY G.yr, G.weekno
ORDER BY Coalesce(G.yr, 9999), G.weekno
;

Ultimately, I really don't like the string manipulation to pull out the date ranges. It would be better if the table had the MinDate and MaxDate as separate columns, and then creating a label for them would be much simpler.

Answer 2

Except when weekof spans years, this will get the data you want and in the correct order:

;WITH x AS 
(
  SELECT weekof, sales, 
    rn = ROW_NUMBER() OVER (ORDER BY [year] DESC, weekno DESC) 
  FROM dbo.table_name
)
SELECT weekof, sales FROM x WHERE rn <= 5
UNION ALL
SELECT MIN(LEFT(weekof, 5)) + ' - ' + MAX(RIGHT(weekof, 5)), SUM(sales)
FROM x WHERE rn > 5    
ORDER BY weekof DESC;

When the rows being returned span a year, you may have to return the rn as well (and just ignore it at the presentation layer):

;WITH x AS 
(
  SELECT weekof, sales, 
    rn = ROW_NUMBER() OVER (ORDER BY [year] DESC, weekno DESC) 
  FROM dbo.table_name
)
SELECT weekof, sales, rn FROM x WHERE rn <= 5
UNION ALL
SELECT MIN(LEFT(weekof, 5)) + ' - ' + MAX(RIGHT(weekof, 5)), SUM(sales), rn = 6 
FROM x WHERE rn > 5
ORDER BY rn;