Select Distinct from single column SQL

Question

I have an SQLite3 Database and I need to select DISTINCT from a specific row (H981.VISITID) from the following query:

`SELECT 
         H981.VISITID AS pat_no, H98.MED_REC_NO 

FROM     H98_Paradigm_Encounters H98
LEFT OUTER JOIN H98_MLO_Encounters H981 
  ON H98.MED_REC_NO=H981.EXTERNALID`

The results are currently producing something like this:

|pat_no|med_rec_no|note|
|1     |4545      |Test|
|2     |4545      |Test|
|3     |4545      |Test|
|4     |4545      |    |
|1     |4545      |    |
|2     |4545      |    |
|3     |4545      |    |
|4     |4545      |Test|

I would like the results to produce something like:

|pat_no|med_rec_no|note|
|1     |4545      |Test|
|2     |4545      |Test|
|3     |4545      |Test|
|4     |4545      |    |

I've tried several things I've seen on here, but nothing seems to work. Any advice?

did you try `DISTINCT` keyword? What is the problem?. Show us db schema, sample data and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, Mar 29 '17 at 13:42
You seem to have a simple question yet you've just dumped a load of SQL code with no details about what it does and no details on the source tables and schemas. You could make a simple example of the code and highlight the problem with some sample data and show what you would like the output to be. Also, we don't want to see 50 odd columns when you are asking about a single column. — Tanner, Mar 29 '17 at 13:44
Now is better, but still you dont say what result you expect? — Juan Carlos Oropeza, Mar 29 '17 at 13:55
With your current example you only need `DISTINCT` check this [**DEMO**](http://rextester.com/PRZX21012) maybe a couple more fields to the sample to understand what is the problem — Juan Carlos Oropeza, Mar 29 '17 at 15:53
I added a field to the example to further explain what the issue is. While column 3 contains varying data, I only need the first example of each. Is it possible to `DISTINCT` just one column? — jbw028, Mar 29 '17 at 17:31
It looks like you want to group by pat_no and med_rec_no in your example desired result, but then you need to decide what you want to do with the "note"s. I can't see where it's coming a from in that example code. — Rory, Mar 29 '17 at 17:40
As the results continue, the 'notes' all repeat themselves over the pat_no's. All I need is one of the results/med_rec_no — jbw028, Mar 29 '17 at 18:29
Which note? (SQL tables are unordered, "first" does not have any meaning.) Does it matter? — CL., Mar 29 '17 at 19:15
Sorry for the delayed response. It does not matter which one — jbw028, Mar 30 '17 at 12:23

score 0 · Answer 1 · edited May 23 '17 at 12:02

You can't DISTINCT by single column, but only but a row:

select DISTINCT

returns the list of unique rows Thing you are looking for is called "group by" and you can't use it for so mutch columns. Why? Lets imagine that you want to get only one value of H981.VISITID, but one value of H981.VISITID refers to two values from H98.DIAGNOSIS_2. Which of them shuld be displayed then? It's not clear.
Just check group by statement or say in comment what you want to receive and I'll help you :)

    SELECT distinct
         H981.VISITID AS pat_no, H98.MED_REC_NO 

FROM     H98_Paradigm_Encounters H98
LEFT OUTER JOIN H98_MLO_Encounters H981 
  ON H98.MED_REC_NO=H981.EXTERNALID`

Will work IF all the values from med_rec_no contain the same value with certain pat_no!! Other way you'll need use group by (check this topic Select first row in each GROUP BY group?)

Select Distinct from single column SQL

1 Answers1