thought I'd post here to see if there was not perhaps a more elegant
and probably quicker solution.
Essentially I have a unique display condition on a table that must
return data in a certain way. The problem column in question is the
second col of a compound PK which thus makes the data in this column
unique for any given col1 value. The problem is that regardles of what
data is in col2, if one of the values is 'NO' ,then this MUST appear
at the top of any resultset, query, or report that can be generated,
otherwise standard alpha sort is fine (it is a varchar(30) field)
My inelegant solution was to create a third column in the table, a
char(1) that can only ever have 2 values, a '1' or a '0'. So all
entries for col2 which are 'NO' get assigned the value 1, all others
are 0 so when you sort using this extra column based on any col1
value, 'NO' always gets to go on top.
Can I lose the extra column which is there purely for sorting/logic
and thus is irrelevant to the table data or its meaning(<hangs head in
due deference to normalisation>) It obviously works OK the way it is,
but it makes all queries/sprocs cumbersome because they always have
the overhead of sorting an additioal column, or an inefficient index
where nearly all the values in a vital column are always '0' except te
odd few, also all the updates and inserts need to be wary of changes
or additions that are make a 'NO' value for col2 and thus reset or set
the sort column to a '1'.
Some sample DDL and values below.
CREATE TABLE [dbo].[tmpTest]
(
[colA] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
[colB] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
[colN] [varchar](50) COLLATE Latin1_General_CI_AS NULL CONSTRAINT
[DF__1] DEFAULT ('UNK'),
[Reference] [char](1) COLLATE Latin1_General_CI_AS NULL DEFAULT
('0'),
CONSTRAINT [PK_tmpText]
PRIMARY KEY CLUSTERED ([colA] asc , [colB] asc)
)
ON [PRIMARY]
insert into tmpTest (colA,colB,colN) values ('1','a','z')
insert into tmpTest (colA,colB,colN) values ('1','b','z')
insert into tmpTest (colA,colB,colN) values ('1','c','z')
insert into tmpTest (colA,colB,colN,reference) values
('2','NO','z','1')
insert into tmpTest (colA,colB,colN) values ('2','a','z')
insert into tmpTest (colA,colB,colN) values ('2','b','z')
insert into tmpTest (colA,colB,colN) values ('2','c','z')
insert into tmpTest (colA,colB,colN) values ('2','d','z')
select * from tmpTest
where colA='2'
order by colA,reference desc,colBYou can use a CASE expression to achieve the same result.
SELECT *
FROM tmpTest
WHERE colA='2'
ORDER BY colA,
CASE WHEN colB = 'NO' THEN 0 ELSE 1 END, colB
--
David Portas
SQL Server MVP
--
