none
Creating a View using DISTINCT and not getting unique results RRS feed

  • Question

  • I am building a view to be used to drill down into a Lightswitch app I'm building and to drive this I want to use a view based off the selection of that value several other values will be given to the user to choose from related to the first selection. I've created a view using the following statement:

    SELECT DISTINCT TOP (100) PERCENT ARSFamily, ARS_Index
    FROM  dbo.csr_standards_cmsars
    ORDER BY ARSFamily

     but the results come back with ALL the records of the source table (509 rows) when there should have only been 29 rows returned (the appropriate number of families or unique groups).  

    The index is necessary to have Lightswitch use the view as a data source.

    Any suggestions as to what I'm doing wrong here?

    Regards,


    Ken Carter


    • Edited by kencar Monday, September 21, 2015 3:31 PM
    Monday, September 21, 2015 3:30 PM

Answers

  • Select   ARSFamily, ARS_Index FROM (
    Select   ARSFamily, ARS_Index, Row_number() Over(Partition by ARSFamily Order by  ARS_Index ) rn
    FROM  dbo.csr_standards_cmsars
    ) t
    where rn =1
     

    • Marked as answer by kencar Monday, September 21, 2015 3:54 PM
    Monday, September 21, 2015 3:38 PM
    Moderator

All replies

  • Get rid of TOP (100) percent and ORDER BY. You can not add ORDER BY to the view.

    Just use

    SELECT DISTINCT ARSFamily, ARS_Index

    from dbo.csr_standards_cmsars


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, September 21, 2015 3:34 PM
    Moderator
  • Select   ARSFamily, ARS_Index FROM (
    Select   ARSFamily, ARS_Index, Row_number() Over(Partition by ARSFamily Order by  ARS_Index ) rn
    FROM  dbo.csr_standards_cmsars
    ) t
    where rn =1
     

    • Marked as answer by kencar Monday, September 21, 2015 3:54 PM
    Monday, September 21, 2015 3:38 PM
    Moderator