Professional Web Applications Themes

DISTINCT and ORDER BY - Microsoft SQL / MS SQL Server

Hi I got this error mes. when i tried to sort som data: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. I dont understand what that means. I cant make any more subquerys because i have alot of parameters in and out. Thankes for any help /Anders...

  1. #1

    Default DISTINCT and ORDER BY

    Hi

    I got this error mes. when i tried to sort som data:

    ORDER BY items must appear in the select list if SELECT
    DISTINCT is specified.

    I dont understand what that means. I cant make any more
    subquerys because i have alot of parameters in and out.

    Thankes for any help
    /Anders
    anders Guest

  2. #2

    Default Re: DISTINCT and ORDER BY

    If you use DISTINCT then every column listed in the ORDER BY list must
    appear in the SELECT list. Therefore this statement in valid:

    SELECT DISTINCT col1, col2
    FROM Sometable
    ORDER BY col2

    but this is not:

    SELECT DISTINCT col1
    FROM Sometable
    ORDER BY col2

    because there is no obvious way for SQL to determine which Col2 value to
    sort on for each distinct value of Col1. GROUP BY may be a better
    alternative:

    SELECT col1
    FROM Sometable
    GROUP BY col1
    ORDER BY MIN(col2)

    If you need more help, please post DDL for your table(s) and some sample
    data as INSERT statements.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: DISTINCT and ORDER BY

    Hummm... it didnt work.



    SQL code:

    ....

    --############# SELECT #############
    SELECT sSQL = sSQL + ' SELECT '
    SELECT sSQL = sSQL + ' anv.anst_nr, '
    SELECT sSQL = sSQL + ' anv.fornamn, '
    SELECT sSQL = sSQL + ' anv.efternamn, '
    SELECT sSQL = sSQL + ' anv.kontonamn, '
    SELECT sSQL = sSQL + ' (bolag.bolag + '''+sOSpace+''' +
    ao_stab.ao_stab + '''+sOSpace+''' + avdelning.avdelning)
    AS organisatoriskenhet '


    --############# FROM #############
    --## Använadare
    SELECT sSQL = sSQL + ' FROM it_reg_anv AS anv '
    SELECT sSQL = sSQL + ' INNER JOIN it_reg_ao_stab AS
    ao_stab ON anv.ao_stab = ao_stab.id '
    SELECT sSQL = sSQL + ' INNER JOIN it_reg_bolag AS bolag
    ON anv.bolag = bolag.id '
    SELECT sSQL = sSQL + ' INNER JOIN it_reg_avdelning AS
    avdelning ON anv.bolag = avdelning.id '


    --## Utrustning
    SELECT sSQL = sSQL + ' ,it_reg_utrustning AS ut '
    --SELECT sSQL = sSQL + ' LEFT OUTER JOIN it_reg_anv AS
    anv1 ON ut.anst_nr = anv1.anst_nr) '
    SELECT sSQL = sSQL + ' INNER JOIN it_reg_typ_av_dator AS
    dator ON ut.typ_av_dator = dator.id '

    ....

    IF(gemensam_dator <> sStandardValue)
    BEGIN
    SELECT sSQL = sSQL + ' AND (anv.gemensam_dator = '''
    + gemensam_dator + ''' ) '
    END

    IF(markt <> sStandardValue)
    BEGIN
    SELECT sSQL = sSQL + ' AND (ut.markt = ''' + markt
    + ''' ) '
    END


    -- ORDER BY ?? ?A?S??AS +ASD???

    EXEC(sSQL)





    =)

    Tanks for any more help

    anders Guest

  4. #4

    Default Re: DISTINCT and ORDER BY

    Please post some code that will actually help reproduce the problem: CREATE
    TABLE statements (simplified to just the essential columns and constraints)
    and your actual query code. Your dynamic SQL query (sSQL) doesn't use
    DISTINCT at all!
    What are the ORDER BY columns you want?

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. select distinct, but order by number of occurances?
    By snapcount@gmail.com in forum MySQL
    Replies: 3
    Last Post: October 9th, 06:57 AM
  2. Using DISTINCT wirh ORDER BY
    By Rustywater in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: July 17th, 06:35 AM
  3. 'distinct on' and 'order by' conflicts of interest
    By stephen@thunkit.com in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: December 31st, 09:51 PM
  4. Replies: 3
    Last Post: April 18th, 12:52 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139