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

• mrichardson 57577

  Right there with Babe

  Points: 739

  More actions

  June 13, 2013 at 9:11 am

  #276339

  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.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 13, 2013 at 9:16 am

  #1623764

  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

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  June 13, 2013 at 9:28 am

  #1623770

  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?

  ---