Default row view

• Jules Bonnot

  SSCertifiable

  Points: 5488

  More actions

  July 27, 2006 at 7:14 am

  #153108

  I have a view which returns a record set full of things and their types.

  So one thing can have many rows, as many rows as the thing has types. What i want to do is define one of those rows as the default row for that thing. So the view has an extra column with a '1' for one row of that thing and 0s in all the other rows relating to that thing. It doesnt matter which row the default is set for as long as there is only one default row per thing.

  I know this can be done with a min or max in a subquery. what i want is a method of keeping this view to a single statement so i can put a clustered index on it.

  -----------------------------------------------------------------

  --DDL

  if object_id('test') is not null

  drop test

  create table test

  (

  id int identity , thing int, type int

  )

  insert test

  select 1, 1

  union all

  select 1, 2

  union all

  select 1, 3

  union all

  select 2, 1

  union all

  select 2, 2

  union all

  select 2, 3

  union all

  select 3, 1

  union all

  select 3, 2

  union all

  select 3, 3

  -----------------------------------------------------------------

  The view should reference table test returning an extra column with a true (1 bit) for one row per thing.

  Thanks very mauch for all help and advise.

  Jules.

  www.sql-library.com[/url]

• Jules Bonnot

  SSCertifiable

  Points: 5488

  More actions

  July 27, 2006 at 8:37 am

  #651892

  in case anyone is interested i solved this by using the sql2005 rank() function

  create view testview

  as

  select

  thing, type,

  rank() over (partition by thing order by thing, type)as Ranking

  from test

  www.sql-library.com[/url]