Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2003
    Location
    São Paulo, Brazil
    Posts
    9

    Unanswered: Selecting "distinct" columns

    Hi all

    I am trying to do a specific SQL statement but am getting very stuck

    Basically I have a table where there is a client_code as one of the columns , this column can contain multiple entries for one client.

    I need to select all the columns and all the rows but only once for each client_code :

    Example

    CLIENT_CODE DESC
    -----------------------------
    1 TEST 1
    2 TEST 2
    1 TEST 3

  2. #2
    Join Date
    Aug 2003
    Location
    São Paulo, Brazil
    Posts
    9

    Sorry hit enter before finishing !!!

    The result I need is as follows :

    CLIENT_CODE DESC
    ----------------------------------

    1 TEST 1
    2 TEST 2

    I do not need the row for CLIENT_CODE = 1 and DESC = TEST 3

    Thanks in advance

    Robin

  3. #3
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Lightbulb Re: Selecting "distinct" columns

    Originally posted by robgray
    Hi all

    I am trying to do a specific SQL statement but am getting very stuck

    Basically I have a table where there is a client_code as one of the columns , this column can contain multiple entries for one client.

    I need to select all the columns and all the rows but only once for each client_code :

    Example

    CLIENT_CODE DESC
    -----------------------------
    1 TEST 1
    2 TEST 2
    1 TEST 3

    For this I would read up on "group by"...

    ...I'm not sure if this would work, but it might...

    select client_code, desc from blah where desc != 'TEST 3' group by
    client_code,desc

  4. #4
    Join Date
    Aug 2003
    Location
    São Paulo, Brazil
    Posts
    9

    Re: Selecting "distinct" columns

    Originally posted by rocket39
    For this I would read up on "group by"...

    ...I'm not sure if this would work, but it might...

    select client_code, desc from blah where desc != 'TEST 3' group by
    client_code,desc
    Thanks but it won't work I do not know the value of the entries that need to left out at runtime iow I do not know (at runtime) that there is a entry with the value 'TEST 3'

    I don't know if grouping will help either as I need to remove the entries that have duplicate client_code 's from the search

  5. #5
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Question Re: Selecting "distinct" columns

    so are you just trying to return one row with the client code without caring about the values in the remaining columns(e.g. CODE_DESC)?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The main problem appears to be that all of the data is in one column (correct me if I am wrong). From the sample, I did this:

    create table forum
    (col1 varchar(15))

    insert into forum values ('1 test 1')
    insert into forum values ('2 test 2')
    insert into forum values ('1 test 3')

    select *
    from forum

    select substring (col1, 1, charindex(' ', col1, 3)), max(convert(int, substring (col1, charindex(' ', col1, 3) + 1, 10)))
    from forum
    group by substring (col1, 1, charindex(' ', col1, 3))

    You may want to substitute min for max, or even average. The use of charindex is assuming that you have only one space between the last element, and the word "test". It works well, if you have variable lengths in your description.

  7. #7
    Join Date
    Aug 2003
    Posts
    1

    Re: Selecting "distinct" columns

    Originally posted by robgray
    Hi all

    I am trying to do a specific SQL statement but am getting very stuck

    Basically I have a table where there is a client_code as one of the columns , this column can contain multiple entries for one client.

    I need to select all the columns and all the rows but only once for each client_code :

    Example

    CLIENT_CODE DESC
    -----------------------------
    1 TEST 1
    2 TEST 2
    1 TEST 3

    I think you can do it this way. But it only works if you have a primary key. Let us name primarykey as "prid" then the query can be like this.

    Select * from tablename where prid in (select min(prid) from tablename group by client_code)

    min(prid) is used to select "Test 1" assuming prid for "Test 1" is less that prid for "Test 3"

  8. #8
    Join Date
    Aug 2003
    Location
    São Paulo, Brazil
    Posts
    9
    Originally posted by MCrowley
    The main problem appears to be that all of the data is in one column (correct me if I am wrong). From the sample, I did this:

    create table forum
    (col1 varchar(15))

    insert into forum values ('1 test 1')
    insert into forum values ('2 test 2')
    insert into forum values ('1 test 3')

    select *
    from forum

    select substring (col1, 1, charindex(' ', col1, 3)), max(convert(int, substring (col1, charindex(' ', col1, 3) + 1, 10)))
    from forum
    group by substring (col1, 1, charindex(' ', col1, 3))

    You may want to substitute min for max, or even average. The use of charindex is assuming that you have only one space between the last element, and the word "test". It works well, if you have variable lengths in your description.
    Sorry the post was a bit misleading there are 2 columns the first is client_code which has the values 1,2 etc and the 2nd column is desc which has the values test 1 , test 2 etc

  9. #9
    Join Date
    Aug 2003
    Location
    São Paulo, Brazil
    Posts
    9

    Re: Selecting "distinct" columns

    Originally posted by rocket39
    so are you just trying to return one row with the client code without caring about the values in the remaining columns(e.g. CODE_DESC)?
    No I am trying to return all the rows and all the columns but not the rows where the CLIENT_CODE is duplicated. Basically I need only one row per client. If the table has 2 or more rows with the same CLIENT_CODE I only need one of them and it does not matter which one.

  10. #10
    Join Date
    Aug 2003
    Location
    São Paulo, Brazil
    Posts
    9

    Re: Selecting "distinct" columns

    Originally posted by rocket39
    so are you just trying to return one row with the client code without caring about the values in the remaining columns(e.g. CODE_DESC)?
    No I am trying to return all the rows and all the columns but not the rows where the CLIENT_CODE is duplicated. Basically I need only one row per client. If the table has 2 or more rows with the same CLIENT_CODE I only need one of them and it does not matter which one.

  11. #11
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Selecting "distinct" columns

    I guess you need a statement like

    select client_code, min(desc)
    from [your table]
    group by client_code
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  12. #12
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Question Re: Selecting "distinct" columns

    Originally posted by DoktorBlue
    I guess you need a statement like

    select client_code, min(desc)
    from [your table]
    group by client_code

    my test shows that that works...but it will not work if you leave off
    the "group by" clause, why is that?

  13. #13
    Join Date
    Aug 2003
    Location
    São Paulo, Brazil
    Posts
    9

    Re: Selecting "distinct" columns

    Originally posted by DoktorBlue
    I guess you need a statement like

    select client_code, min(desc)
    from [your table]
    group by client_code
    Ah thank you it works !!!! Why do I never think of the simple solution !!!

  14. #14
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Selecting "distinct" columns

    Rocket it's a kind of basic SQL: the GROUP BY clause tells the DB engine to groups all distinct occurences of client_code. All you need to do is to get one of the desc's per group, i.e. per client_code. I took the minimum, but the maximum works too.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  15. #15
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Re: Selecting "distinct" columns

    Originally posted by DoktorBlue
    Rocket it's a kind of basic SQL: the GROUP BY clause tells the DB engine to groups all distinct occurences of client_code. All you need to do is to get one of the desc's per group, i.e. per client_code. I took the minimum, but the maximum works too.
    Thanks...understood the purpose of the group by, but was curious as to the requirement of the "group by" in order for the statement to not fail, but it would appear that the 'client code' could not be selected since it was not part of the aggregate function nor used in a "group by" statement. (just wondering what part of a system requirement it fulfilled by including it in a group by clause...)

Posting Permissions

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