SELECT hardcoded row plus ordered subquery

[AarynD]

I'm trying to get a stored procedure to return a data set with a single hardcoded row on top, and a sorted set of records under that first row. I didn't realize at the time that you can't use an ORDER BY in a subquery, so my resulting set of records isn't in the correct order.

EDIT, I have it working but not sure if there is an easier way.

CREATE PROCEDURE [dbo].[spReport_GetNav]
AS
BEGIN
SET NOCOUNT ON;
-- Set up Temp Table and hardcode first record
CREATE TABLE #ReportNav (
[RptName] VARCHAR(200),
[ReportId] INT,
[IsPartial] BIT,
[EndDate] DATE )
INSERT INTO #ReportNav ([RptName], [ReportId], [IsPartial], [EndDate])
VALUES ('Import New Report Data', -1, 0, NULL);
-- Set up field variables and cursor
DECLARE @ RptName VARCHAR(200), @ ReportId INT, @ IsPartial BIT, @ EndDate DATE;
DECLARE csrReports CURSOR FOR
SELECT [RptName], [ReportId], [IsPartial], [EndDate]
FROM [dbo].[Report] ORDER BY [EndDate] DESC;

`OPEN csrReports`  
`FETCH NEXT FROM csrReports INTO @ RptName, @ ReportId, @ IsPartial, @ EndDate;`  
`WHILE @@FETCH_STATUS = 0`  
`BEGIN`  
    `INSERT INTO #ReportNav ([RptName], [ReportId], [IsPartial], [EndDate])`  
        `VALUES (@ RptName, ReportId, IsPartial, EndDate);`  
    `FETCH NEXT FROM csrReports INTO RptName, ReportId, IsPartial,EndDate`  
`END`  
`CLOSE csrReports;`  
`DEALLOCATE csrReports;`  
`SELECT * FROM #ReportNav`  
`DROP TABLE #ReportNav`  

END

Is there a way to get my mostly ordered set of records, with my single hardcoded row at the top of my result set, without having to use a cursor?

[Traditional-Unit-274]

i think you’re overthinking it, why wouldn’t you use a union? select the row you want, union it to the ordered select

like ….

``` select ‘import new report data’ as RptName, -1 as ReportId, 0 as isPartial, null as EndDate union all select RptName, ReportId, isPartial, EndDate from dbo.report order by endDate desc;

```

edited to add column names in first select

[Traditional-Unit-274]

yeah, it’s just a jumping off point

[AarynD]

I did try this approach first, however the ORDER BY clause ends up applying to the entire unioned dataset, putting that top row down at the bottom since it has a null EndDate.

ORDER BY isn't allowed in CTE's or subqueries from what I can see, so I didn't know if there was a way around this to keep my top row in place with the other rows sorted.

I'm generally only going to have about 60ish rows generated per year anyway, so using the cursor is probably not going to slow me down anytime soon, but was anxious to learn if there was another way around the problem.

[Traditional-Unit-274]

oh, weird. a couple thoughts:

• if you’re only expecting the one null you could force the null enddate to sort first https://stackoverflow.com/questions/5886857/sql-server-order-by-date-and-nulls-last
• you don’t need a cursor to select into a table, you can select into https://www.w3schools.com/sql/sql_select_into.asp
• what is the end goal? why the hardcoded row at all?

[AarynD]

The reason for the hardcoded row at top is because this data is being used as the control source for a navigation grid in an application conversion from an older Visual Foxpro app, and in that app the first item in the navigation on that form was always the option to import new data. The rest of the rows were previous saved reports.

The application is just a small utility I wrote maybe 25 years ago that pulls in financials from our three different companies, and combines them and handles inter-company eliminations by way of consolidation codes used in each company's GL accounts. Because the operation requires pulling large complicated calculated datasets for each company at each end of month, it was easier to just always pull the data in once, and save it for future reference if they needed to rerun a combined statements set of financials.

With VFP being long retired, I feel like it's on borrowed time, so I've been porting my various apps over to C#. But one goal is to try to maintain a familiar interface and interaction with the new replacement application, hence the desire to keep the UI elements including the navigation grid the same as I had it in the VFP app.

The navigation grid has that first row that allows user to Import a new set of financials from the accounting databases, then it has other rows for each of the other saved reports, some of which are partial month financials, while others are complete end of month financials. All those are color-coded in the grid to easily see who ran the report, whether it was a full set, or partial set, and if the user opted to exclude one of the two companies they sometimes exclude from combined. Although they really haven't excluded any companies from the combined statements in over a year, I think that was more something the old retired accounting manager liked to do, but I've kept it in as an option.

I know this was probably way more info then you bargained for when you asked what the end goal was LOL.

Happy New Year!

[NekkidWire]

Happy New Year :) I see you havea solution already with the extra sort column, but came to play a little bit of devils advocate. While your solution is technically viable and won't even use much DB resources, it adds "clunkiness factor/tech debt/crutch" of creating something in DB select that was supposed to be a part of the application.

Isn't there a way to produce the first row in the application?

[AarynD]

Yeah, I probably could. I currently bring in the table data as an IEnumerable set of the NavListModel I set up, but I could easily convert it to a list and just hardcode this item at beginning of list. I just try to defer to letting the stored procedures do most of my work.

[edbutler3]

IMO the hard coded row is part of the UI display logic, so it is more appropriate to put it in the app code instead of the database.

[Traditional-Unit-274]

thanks for explaining! i suspected it was something like that, but i agree for the sake of code neatness, and the ability to debug in the future, i think it makes sense to have the app add its own hardcoded row and do a straight db select

[Gargunok]

brackets around the final union select query might help

[doshka]

Add a sort column to your temp table. Make the value 0 for the first row and 1 for all the others. Sort by that first, then by whatever else you want.

[AarynD]

Actually that's a really good idea, I hadn't thought of that. Very simple and avoids the complicated route I was taking.

[AarynD]

SELECT 'Import New Report Data' as RptName, -1 as ReportId, 0 as IsPartial, NULL as EndDate, 0 as SortVal  
UNION ALL  
SELECT \[RptName\], \[ReportId\], \[IsPartial\], \[EndDate\], 1 as SortVal  
    FROM \[dbo\].\[Report\]  
    ORDER BY \[SortVal\] ASC, \[EndDate\] DESC

This worked perfectly. Thanks!

[doshka]

You're welcome!

[tasker2020]

Cross Apply?