July 15, 2015 at 12:45 pm
I see Oracle and some other DB have Order By Some_Column DESC NULLS LAST
For SQL server 2008 - is there something like this?
Otherwise, there seems to be various more complex ways to accomplish the result.
Thanks!
Sort: Area (Text) then Category (Text)
A query - with Sort By on a text field.
The text field can have a value (Format: Category <space> Number)
OR Null (name has yet to be assigned)
No problem to sort the Category.
The customer wants all of the Nulls at the bottom.
The Sort would look like this:
Area 1 Category1
Area 1 Category2
Area 1 Category3
Area 2 Category1
Area 2 Category2
Area 2 Category3
Area 1
Area 1
Area 1
Area 2
Area 2
Area 2
A default Acending puts the Null Categories at the top.
Any ideas appreciated
July 15, 2015 at 1:04 pm
Does not exist in SQL Server but a workaround could be adding a sort column
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TEST_NULL_SORT TABLE (AREA VARCHAR(50) NOT NULL, CATEGORY VARCHAR(50) NULL);
INSERT INTO @TEST_NULL_SORT
VALUES
('Area 1','Category1')
,('Area 1','Category2')
,('Area 1','Category3')
,('Area 2','Category1')
,('Area 2','Category2')
,('Area 2','Category3')
,('Area 1',NULL)
,('Area 1',NULL)
,('Area 1',NULL)
,('Area 2',NULL)
,('Area 2',NULL)
,('Area 2',NULL);
;WITH SORTED_SET AS
(
SELECT
TNS.AREA
,TNS.CATEGORY
,ROW_NUMBER() OVER
(
ORDER BY TNS.AREA
,TNS.CATEGORY
) +
CASE
WHEN TNS.CATEGORY IS NOT NULL THEN 1
ELSE 99999999
END AS SORT_ORDER
FROM @TEST_NULL_SORT TNS
)
SELECT
SS.AREA
,SS.CATEGORY
FROM SORTED_SET SS
ORDER BY SS.SORT_ORDER
;
Results
AREA CATEGORY
------- ----------
Area 1 Category1
Area 1 Category2
Area 1 Category3
Area 2 Category1
Area 2 Category2
Area 2 Category3
Area 1 NULL
Area 1 NULL
Area 1 NULL
Area 2 NULL
Area 2 NULL
Area 2 NULL
July 16, 2015 at 9:17 am
On the SQL Server View:
SELECT vWells_Standard.Area, vWells_Standard.PadNameFinal, vWells_Standard.[Well Name], ISNULL(vWells_Standard.PadNameFinal, N'zzz') AS PadNameNullSort
Thanks, that helped! This is my slight modification.
My MSAccess uses SQL Server Native Cleint 11.0 linked tables. Any Order By on the SQL Server View is lost by the MSAccess client.
On the MSAccess side, if the Linked Table is used in a Query that has a pure SQL Where clause, the ODBC will push that to the server side to run.
By creating a new column for MS Access Query to sort on "PadNameNullSort" the nulls now move to the last.
The "PadNameNullSort" returns the PadNameFinal or zzz
July 16, 2015 at 11:53 am
Here is another way to accomplish the task with no need to create a sort column in a CTE.
SET NOCOUNT ON;
DECLARE @TEST_NULL_SORT TABLE (AREA VARCHAR(50) NOT NULL, CATEGORY VARCHAR(50) NULL);
INSERT INTO @TEST_NULL_SORT
VALUES
('Area 1','Category1')
,('Area 1','Category2')
,('Area 1','Category3')
,('Area 2','Category1')
,('Area 2','Category2')
,('Area 2','Category3')
,('Area 1',NULL)
,('Area 1',NULL)
,('Area 1',NULL)
,('Area 2',NULL)
,('Area 2',NULL)
,('Area 2',NULL);
SELECT
SS.AREA
,SS.CATEGORY
FROM @TEST_NULL_SORT SS
ORDER BY
case when SS.CATEGORY is null then 0 else 1 end desc,
SS.AREA
,SS.CATEGORY;
July 16, 2015 at 12:02 pm
Lynn Pettis (7/16/2015)
Here is another way to accomplish the task with no need to create a sort column in a CTE.
SET NOCOUNT ON;
DECLARE @TEST_NULL_SORT TABLE (AREA VARCHAR(50) NOT NULL, CATEGORY VARCHAR(50) NULL);
INSERT INTO @TEST_NULL_SORT
VALUES
('Area 1','Category1')
,('Area 1','Category2')
,('Area 1','Category3')
,('Area 2','Category1')
,('Area 2','Category2')
,('Area 2','Category3')
,('Area 1',NULL)
,('Area 1',NULL)
,('Area 1',NULL)
,('Area 2',NULL)
,('Area 2',NULL)
,('Area 2',NULL);
SELECT
SS.AREA
,SS.CATEGORY
FROM @TEST_NULL_SORT SS
ORDER BY
case when SS.CATEGORY is null then 0 else 1 end desc,
SS.AREA
,SS.CATEGORY;
Good catch Lynn, this eliminates the second sort, close to 30% more efficient than my suggestion! Obviously I should have realized that the first sort could not be reused:pinch:
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply