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

Question

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.

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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: 246962 More actions · Answer 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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin