• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

How to store records so that they can be retrieved in the same record order in which they were stored as a RESULT SET based on a SELECT statement?

How would you modify the following SQL SELECT Statement using SQL Server 2005, so that the Result Set is saved into a temp table and then the contents of this temp table's records can be displayed in the row order in which the records were stored?

select CASE WHEN (GROUPING(MthTitle) = 1)  THEN 'TOT:'
            WHEN (GROUPING(OfficeNumber) = 1) THEN 'SUB:'
            ELSE MthTitle
       END AS MthTitle
     , DollarTitle, OfficeNumber, CustomerNumber, FirstName, LastName, StreetAddr1, City,
ResStateCode, DateLost
     , sum(AcctValue) AcctValue
from tblSummMod td
group by MthTitle, DollarTitle, OfficeNumber, CustomerNumber, FirstName, LastName, StreetAddr1, City,
ResStateCode, DateLost with rollup
having (GROUPING(OfficeNumber) = 1 and GROUPING(DollarTitle) = 0)
    or GROUPING(DollarTitle)
       + GROUPING(CustomerNumber)
       + GROUPING(FirstName)
       + GROUPING(LastName)
       + GROUPING(StreetAddr1)
       + GROUPING(City)
       + GROUPING(ResStateCode)
       + GROUPING(DateLost) = 0
      or GROUPING(MthTitle) = 1
order by DollarTitle Desc,
         CASE WHEN (GROUPING(MthTitle) = 1)  THEN char(255)
              ELSE td.DollarTitle
       , CASE WHEN (GROUPING(OfficeNumber) = 1) THEN char(254)
              ELSE td.MthTitle
       , AcctValue desc
  • 2
1 Solution
The only way to guarantee the order of any query is to use an ORDER BY clause. Therefore, if you need a specific order, populate a column with a value that matches the desired order. For example, you might increment a column value by (1) each time you inserts a record. The sequential numbers would allow sequential retrieval.

Are you creating the table or is it an existing table that you must use?

zimmer9Author Commented:
I want to store the RESULT SET of the SQL statement above into a NEW table and export this newly created table to an Excel file. Can you think of way of doing so? Is there a way to perform a SELECT statement on the NEWLY created table records to export the records in the same order as would be the case of executing the SQL Statement shown above.

Here is my issue. I'm writing a C# program. I want to create a STORED PROCEDURE and then I pass the stored procedure to a routine that executes the STORED PROCEDURE and populates the result set into an Excel file. And the records should be displayed with detail records, followed by Sub total, followed by more detail records, followed by another Sub total and finally a TOTAL record within the exported file to Excel.
And again, yes, you can specify an order by using ORDER BY. SQL doesn't supply any other way. Your SQL includes ORDER BY. Whatever consumes the result set will receive the rows according to your ORDER BY. If it writes output in an Excel file format (or will it be .CSV or some other non-Excel format?), the output will be in the order of the result set.

If it adds sub-total and total records at your desired points, then they'll be in the order of your SQL statement.

Is your stored proc going to execute your SQL? If it is, then your routine will receive rows in order, process groups in order, output sub-totals in order and output a final total at the end.

Or is your routine first going to INSERT each result set row into your 'NEW' table, and then after it's finished there, it will start retrieving rows from the 'NEW' table and use those new rows to build the Excel output with sub-totals?


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now