how can i select distinct values from 1 column in my query

  • I would like to select distinct values from 1 column in my query.

    If I was selecting just 1 column in a simple query it would probably work, but I've got a more complex query and having trouble with it.

    here is my code:

    SELECT db.myTable.myFieldId, db.myFields.ModuleId, db.myTable.FieldValue

    FROM db.myTable INNER JOIN

    db.myFields ON db.myTable.myFieldId = db.myFields.myFieldId INNER JOIN

    db.myRows ON db.myTable.myRowId = db.myRows.myRowId

    WHERE (db.myFields.ModuleId = 1397) AND (db.myTable.myFieldId = 221)

    The part I want to be distinct is this bit: db.myTable.FieldValue

    If I insert the word 'distinct' before it, an error returns saying I can't do this.

    thanks,

    mark.

  • It's easy, but you need to define which value of db.myTable.FieldValue do you want when multiple values are available.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • mrichardson 57577 (6/13/2013)


    I would like to select distinct values from 1 column in my query.

    If I was selecting just 1 column in a simple query it would probably work, but I've got a more complex query and having trouble with it.

    here is my code:

    SELECT db.myTable.myFieldId, db.myFields.ModuleId, db.myTable.FieldValue

    FROM db.myTable INNER JOIN

    db.myFields ON db.myTable.myFieldId = db.myFields.myFieldId INNER JOIN

    db.myRows ON db.myTable.myRowId = db.myRows.myRowId

    WHERE (db.myFields.ModuleId = 1397) AND (db.myTable.myFieldId = 221)

    The part I want to be distinct is this bit: db.myTable.FieldValue

    If I insert the word 'distinct' before it, an error returns saying I can't do this.

    thanks,

    mark.

    If your base data was like this:

    (myFieldId, ModuleId, FieldValue)

    (1,2,1)

    (3,4,1)

    and you want 'FieldValue' to be distinct, which version of myFieldId and moduleId would you want to display?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply