SQL Distinct on one column only


SQL Distinct on one column

I have come across the problem I am writing about in the past and never found a solution for it online. It may have been due to the search words I was using or that SQL gurus find this query elementary and no one would ask.

In any case, what I am talking about is retrieving two or more rows but filtering where only one of the columns in the multiple columns you are querying for is distinct.

For example you have the following dataset:

TABLE: Transactions For Day

CUST_NUM ORDER_NUM ITEM_NUM TIME
5263 1236 A5302 10:34:40
5263 1236 A2503 10:34:43
5263 1236 A2503 10:34:47
5263 1236 A2503 10:34:53
5263 1236 A2503 10:35:08
5263 4507 A3603 11:10:25
5263 4507 A3843 11:10:37
5263 8302 A5302 13:10:25
4356 5336 A5302 10:12:12
3540 3636 A5302 10:12:17
3540 3636 A3603 10:12:25
3540 3636 A3754 10:12:32
3540 3636 A3852 10:12:43
3540 8302 A3603 8:45:52
3540 8302 A2503 8:46:08
3540 8302 A2525 8:46:12
3540 8302 A2653 8:46:18
3540 8302 A2758 8:46:32

The above dataset (table) has 4 fields, customer number, order number, item number and time. If you notice each transaction records the cusotmer number, the order number, the item number and the time each item was rung up for an order. So a cumstomer number and order number will appear as many times as there is a different item purchased with each item having its unique timestamp.

Now let’s say that you want to query distinct customers with a distinct order number and want to get an idea of the time they made their purchase. Something more or less like this:

CUST_NUM ORDER_NUM ITEM_NUM TIME
5263 1236 A5302 10:34:40
5263 4507 A3603 11:10:25
5263 8302 A5302 13:10:25
4356 5336 A5302 10:12:12
3540 3636 A5302 10:12:17
3540 8302 A3603 8:45:52

 

If you were to say,

SELECT distinct CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
From Transactions

You would get every row.

However if you were to query it in this manner:

SELECT CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
FROM Transactions A
RIGHT JOIN (SELECT DISTINCT CUST_NUM FROM Transactions) AS TR
ON TR.CUST_NUM = A.CUST_NUM

The preceding query was tested on Teradata.

Example using the MySQL database.

Also, I have tested the query below using the dataset above under MySQL.

SELECT CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
FROM Transactions
GROUP BY CUST_NUM, ORDER_NUM
ORDER BY CUST_NUM DESC, ORDER_NUM

Then you would get the ideal set of rows we are after.

CUST_NUM ORDER_NUM ITEM_NUM TIME
5263 1236 A5302 10:34:40
5263 4507 A3603 11:10:25
5263 8302 A5302 13:10:25
4356 5336 A5302 10:12:12
3540 3636 A5302 10:12:17
3540 8302 A3603 8:45:52

MySQL Screenshots: All Transacions -

Transactions - All Records MYSQL SQL Query for Uniquie Transactions Transactions Query Results
I’m sure there are other ways, but if you ever asked yourself, how to I return multiple columns with only one row being distinct, well there it is.

Multiple columns returned and based on only one distinct row.

Example using the Oracle Database

select a.cust_num, a.order_num, b.item_num, b.inv_time
from (select distinct order_num, cust_num from trans_table) a
right join (select max(cust_num) as cust_num, order_num, max(item_num)as item_num, max(inv_time) as inv_time from trans_table
group by order_num) b
on b.order_num = a.order_num
order by order_num

select a.cust_num, a.order_num, b.item_num, b.inv_time
from (select distinct order_num, cust_num from trans_table) a
right join (select max(cust_num) as cust_num, order_num, max(item_num)as item_num, max(inv_time) as inv_time from trans_table
group by order_num) b
on b.order_num = a.order_num
order by order_num

About these ads

65 Comments

Filed under SQL

65 responses to “SQL Distinct on one column only

  1. Not working for me. I’m using…

    SELECT first_name, last_name, email_address
    FROM Person A (nolock)
    RIGHT JOIN (SELECT DISTINCT email_address FROM Person) AS TR
    ON TR.email_address = A.email_address

    Error :
    Server: Msg 209, Level 16, State 1, Line 1
    Ambiguous column name ‘email_address’.

  2. oscarvalles

    You are using SQL Server right?

    For email address in the distinct statement, try giving email address an alias like ‘emad’.

    SELECT first_name, last_name, email_address
    FROM Person A (nolock)
    RIGHT JOIN (SELECT DISTINCT email_address as emad FROM Person) AS TR
    ON TR.emad = A.email_address

    That should take care of the ambiguous column name error.

    I haven’t used SQL server a lot, but if that doesn’t get the query your looking for, let me know.

  3. AdamD

    I’ve got a non-technical database question.
    I downloaded Oracle for playing with at home.
    What is a good database to download to play with?
    I’d like one that has interesting data, but with multiple tables
    and multiple relations between the tables.

    I found IMDB in database format. It has tables for person (actor/
    director) movie, quote etc. Interesting content, multiple
    relationship-types between some of the tables.
    I’m looking for other fun and useful databases – CDDB (list
    of CD titles and artists etc) maybe. What else? Has anyone
    done a freeware alcoholic drink recipies database? (I have
    some Kahlua and some creme de menthe, what can I make…?)
    Anything other interesting databases?

    • Aishya

      Hi Adam,
      I am learning SQL and databases to practice on. You have mentioned IMDB is interesting.
      Could you please email me the IMDB in database format and other databses you have collected. I would like to work on them.My email id is aishyaraghav @yahoo.com.
      Thank You,
      Aishya

  4. Hi Adam,

    Thanks for your interesting comments on all posts. Regarding a database to download and play with at home, I would recommend MySQL. It is light, easy to use and if you are interested in web development it plays well with PHP. You can get MySQL in a number of places, but perhaps one installer that will provide you a web development environment to expirement with on your PC (including MySQL) is XAMMP. You can download this from http://www.apachefriends.org/en/index.html

    Your probably thinking, “I just asked about a database.” But with the XAMMP install, you will have access to an administrative backend for MySQL called phpMyAdmin. Here you can administer your database environment via a web user interface.

    As far as the datasets, I don’t know of any real interesting ones such as the IMDB one you have but the census bureau offers access to a lot of demographic data that you may find interesting. There is also an online application called data ferret (http://dataferrett.census.gov/) that you can utilize to download data in text file formats. I mention text files since you can upload text files of data into MySQL and easily create new tables with file imports.

    Anyway, if you have any questions during your search for interesting data, just post. I promise not to take as long to reply next time.

  5. SQL Joins get me every time, cheers for the above, most helpful.

    Mike

  6. Dear Oscar, I think there is a slight mistake in your sql sample code above:

    You wrote:

    SELECT CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
    FROM Transactions A
    RIGHT JOIN (SELECT DISTINCT CUST_NUM FROM Transactions) AS TR
    ON TR.CUST_NUM = A.CUST_NUM

    But it should be:

    SELECT CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
    FROM Transactions A
    RIGHT JOIN (SELECT DISTINCT ORDER_NUM FROM Transactions) AS TR
    ON TR.CUST_NUM = A.CUST_NUM

    You would want to select distinct by Order_Num, based on the goal you are pursuing in your example.

    Drop me an email :)

    Cordially,

    Agustin Garzon.

    • Khadam Sheikh

      Hi Agustin,

      You wrote as
      SELECT CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
      FROM Transactions A
      RIGHT JOIN (SELECT DISTINCT ORDER_NUM FROM Transactions) AS TR
      ON TR.CUST_NUM = A.CUST_NUM

      It should be as
      SELECT CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
      FROM Transactions A
      RIGHT JOIN (SELECT DISTINCT ORDER_NUM FROM Transactions) AS TR
      ON TR.ORDER_NUM = A.ORDER_NUM

  7. James

    Most helpful, been stuck on this prob… now can get on with work! Thx!

  8. wish I found this about 2 hours ago – Thanks! very helpful

  9. Sudeep

    I need to select distinct name from a table (id, name) in oracle. So i need something like

    SELECT id, DISTINCT name form table;

    But it gives error. How could I do this ? Please Help.

  10. Lewis

    How would this SQL Distinct on one column only problem be solved in Oracle? I can’t seem to get it to work.
    Thanks Much.

  11. Lewis

    Thanks Oscar,

    I was just testing it on some databases I had, both in Access and in something called Oracle Express that I can access online. Neither worked. I don’t have a current project, but I just want to know this. I just used your code, which is below, and plugged in my own table and columns. It just didn’t work. But, also, I don’t know why Oracle would be any different.

    SELECT CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
    FROM Transactions A
    RIGHT JOIN (SELECT DISTINCT CUST_NUM FROM Transactions) AS TR
    ON TR.CUST_NUM = A.CUST_NUM

    Thanks Much,
    Lewis Billingsley

    • I just installed Oracle Express and I am stumped. Now I really want to figure this out. I know MySQL acts differently than Teradata, that’s why I included the MySQL example in the post. I think the SQL also worked for me with DB2 at some point, but I really can’t recall. Nevertheless, I’m going to keep messing with it via Oracle Express. I’ll post once I figure it out (or someone gives us the answer).

      Okay I figured it out. I changed the column time to inv_time in my example. Time is usually a reserved word in databases so I apologize for initially naming that column TIME.

      The following SQL should work. I’ve posted a print screen of Oracle Express in the post.

      select a.cust_num, a.order_num, b.item_num, b.inv_time
      from (select distinct order_num, cust_num from trans_table) a
      right join (select max(cust_num) as cust_num, order_num, max(item_num)as item_num, max(inv_time) as inv_time from trans_table
      group by order_num) b
      on b.order_num = a.order_num
      order by order_num

      Thanks Lewis

      • silversurfer20

        I realise this is a really old thread but just on the offchance that someone might be able to help, I’m trying to solve the same problem and am using the code suggested above with:

        SELECT IAURN, CNAME, Address1, Address2, Address3, Town, County, Postcode, Telephone, tps_flag, SIC92_5code, total_emps, Title, Forename, Surname, JobTitle
        FROM [PRS Sector Groups Selection IDS] A
        RIGHT JOIN (SELECT DISTINCT IAURN as ID FROM [PRS Sector Groups Selection IDS]) AS TR
        ON TR.ID = A.IAURN

        I’m trying to make the IAURN unique. The query runs but it just produces the same data as I have in the original table. :S Any help, greatly appreciated.

      • What database are you using. I haven’t tried the described technique on SQL Server, so it may be different.

  12. silversurfer20

    I was attempting to do it in Access. We’ve now managed to solve the problem using a bit of software but I never could get the query to work properly, most likely just because I had used it incorrectly. Thank you for taking the time to reply to my post.

  13. Chris

    In MySQL you can just do:

    SELECT DISTINCT CUST_NUM, ORDER_NUM, ITEM_NUM, TIME
    From Transactions
    GROUP BY CUST_NUM

    It will group by CUST_NUM and make that column distinct.

  14. JC

    Thank you sir! :D I assumed it was something like this, but having a blueprint helps.

  15. Deon

    Hey i am having the same issue. I am using ms sql 2005. My code is as follows:

    SELECT dscr,
    amt_owed,
    amt_paid,
    amt_dismissed,
    case_id,
    case_cd,
    addr_line1,
    addr_line2,
    city,
    st_cd,
    zip_cd,
    upd_dttm,
    first_name,
    last_name,
    addr_cd
    FROM tbl_WU_Speedpay t1
    RIGHT JOIN(SELECT DISTINCT dscr AS CaseNumber FROM tbl_WU_Speedpay) AS t2
    ON t2.CaseNumber = t1.dscr

    i am trying to get unique dscr but i keep getting repetitive values in this field. Any help or suggestions would be greatly appreciated.

    • I need to update this post and your question is a reminder why, so thank you for that.

      Now, to your question, if case_id is a key field, then you can try the query below. It is much simpler and you should get what you are looking for. Email me if you have problems. Or text 512-481-2451.

      Also, I think the ON statement on your JOIN CLAUSE may have been mistyped. That is assuming “dscr” stands for description. But either way, I think the method below is simpler and will provide what you need.

      SELECT max(dscr) AS dscr,
      MAX(amt_owed) AS amt_owed,
      MAX(amt_paid) AS amt_paid,
      MAX(amt_dismissed) AS amt_dismissed,
      case_id,
      MAX(case_cd) AS case_cd,
      MAX(addr_line1) AS addr_line1,
      MAX(addr_line2) AS addr_line2,
      MAX(city) AS city,
      MAX(st_cd) AS st_cd,
      MAX(zip_cd) AS zip_cd,
      MAX(upd_dttm) AS upd_dttm,
      MAX(first_name) AS first_name,
      MAX(last_name) AS last_name,
      MAX(addr_cd) AS addr_cd
      FROM tbl_WU_Speedpay t1
      GROUP BY case_id

      Perhaps a more efficient coding solution is:


      SELECT *
      FROM(
      select *, row_number() over(partition by case_id order by case_id) as val
      from [database].[dbo].[table]
      ) t
      WHERE t.val = 1

      • Deon

        Thank you so much for your response. I just thought i would take a shot in the dark on that one because i knew this thread was kind of old. I think this is what i need but what i need to be unique is the dscr field. For instance, i need to get the same number of records in my query that would match what i get when i run :
        select distinct dscr
        from tbl_WU_Speedpay

  16. Deon

    Thank you. This solution worked well for me. Thanks

  17. Prada

    Isn’t there any easier way of doing this in MS SQL Server other than using ‘MAX()’ and ‘GROUP BY’ as my table contains more than 30 columns.

    In addition to this I am using ORM.

    • Yes, and thank you for asking otherwise I don’t think I would have attempted to find a solution for it. The following is applicable to SQL Server 2005 through 2008.

      select *
      from(
      select *, row_number() over(partition by distinct_col order by distinct_col) as seq
      from [database].[dbo].[table]
      ) t
      where t.seq = 1

      where distinct_col is going to be the column that you want to group by, that is the one that you want to base your unique values off of. On SQL Express 2008, querying 6.5 million records based on 63 unique values with no indexes on tables, this query took 2.5 minutes to complete, so it may be computationally expensive.

      • Prada

        Thank you for you quick response.

        I tried it and works in MS SQL Server 2005 and above. But I need this in MS SQL Server 2000 as well. I tried to implement same technique in different way (i.e. creating row number) but it takes too long to execute(this table of mine is too big). Any simple idea how to do this in SQL Server 2000?

        Thanks in advance.

      • Sorry don’t have SQL Server 2000 to test on. I’m sure you’d already thought of this, but if you have the sufficient space in SQL Server 2005, it may be easier to export the databases over.

        Also, if you’re still open to creating a sequence number based on groups, here is a great post that helped me accomplish this in the past: http://www.tek-tips.com/viewthread.cfm?qid=859372

  18. Prada

    Thanks a lot for tips. I’ll try. If it works then I’ll reply back to you. :)

  19. SELECT name, (select distinct id from table) from table

    I would like to display 2 columns, but only the id is distinct. How can i select the distinct id and displaying the name if I am using progress database?

    • I haven’t worked with Postgre SQL really but there are two possible solutions depending on your table.

      1.) if you have one unique name for every id, then the following SQL will work
      SELECT DISTINCT id, name FROM table
      But that doesn’t seem to be the case, so . . .
      2.) If multiple people can have the same ID (for example Jake and Mary have ID, 123) then try
      SELECT id, MAX(name) FROM table

      Unfortunately I don’t have Postgre so I can test the second scenario, but most of the databases that I’ve worked with have allowed the use of MAX() function with characters. If it doesn’t work, feel free to post another question.

      Oscar V

  20. Alo

    Hi,
    I have the table:
    id userid value date
    1 1 val1 2011-05-08
    2 1 val2 2011-06-09
    3 2 val3 2011-06-09
    4 3 val4 2011-06-09
    5 3 val5 2011-04-09
    6 4 val6 2011-04-09
    7 4 val7 2011-06-05
    8 4 val8 2011-06-01
    And i want get latest user row ( order by date desc and group by userid )
    Result should be like this:
    id userid value date
    2 1 val2 2011-06-09
    3 2 val3 2011-06-09
    4 3 val4 2011-06-09
    7 4 val7 2011-06-05

    How can I do this using MSSQL?
    Thanks!

    • Hi Alo,

      Sorry for the late reply. Still on vacation. Here is what you need

      SELECT A.[id]
      ,B.[userid]
      ,A.[value]
      ,B.[date]
      FROM [sandbox].[dbo].[alo] A
      RIGHT JOIN (SELECT [userid] ,max([date]) as [date]
      FROM [sandbox].[dbo].[alo]
      GROUP BY [userid]) B
      ON B.[userid] = A.[userid]
      AND B.[date] = A.[date]

  21. This is quite helpful, but I am unable to get it to work for my case. Can you please advise?

    I am using Oracle 11g.

    I have a users table that has columns username,company and I would like to get a list of distinct usernames and include the company as well.
    The following code works fine:

    select distinct username,company from users order by 1;

    because every account is by definition given a distinct username.
    Here is a sample of the table

    USERNAME          COMPANY
    abc-rt            The Alpha-beta Company
    abc-sw            The Alpha-Beta co
    abc-wr            The Alpha-Beta co
    grr-as            Tigers Laundry
    grr-ed            Tigers Laundry
    grr-fg            Tigers Laundry
    grr-nk            Tigers Laundrey
    grr1              Tigers Laundry
    

    What I need to do is get a list of distinct usernames when I trim off the dash and initials.
    So the result I am looking for would be:

    abc               The Alpha-beta Company
    grr               Tigers Laundry
    grr1              Tigers Laundry
    

    I have been able to successfully strip off the the dash and initials to get the distinct username using the following code:

    select distinct
    (substr
    (u.username,1,
    (case
    when instr(u.username,'-')-1 <= 0 then 100
    else instr(u.username,'-')-1
    end
    )
    )
    ) as DU
    from users u
    order by 1;

    which returns:

    abc
    grr
    grr1
    

    Unfortunately, when I add in the company name to the select statement

    ) as DU, company

    I get the following results

    abc               The Alpha-beta Company
    abc               The Alpha-Beta co
    grr               Tigers Laundry
    grr               Tigers Laundrey
    grr1              Tigers Laundry
    

    because of the unique spellings in the Company field.
    Unfortunately, I can not do the most obvious solution of fixing all the spelling differences, as I don’t have permissions to insert/update/create tables.
    I don’t really care which spelling of the company name is returned, if that makes it easier.
    I would appreciate any assistance you can provide.
    Thanks, Dave

    • Hey Dave,

      I just finished installing Oracle 10g Express and tested the solution below. The trick is to apply the MAX() function to the “COMPANY” column and group by the neat char stripping function to put together. Thanks for your question. ~ ov


      select distinct
      (substr(u.username,1,
      (case
      when instr(u.username,'-')-1 <= 0 then 100
      else instr(u.username,'-')-1 end))) as DU
      ,max(company)
      from users u

      group by
      (substr (u.username,1,
      (case
      when instr(u.username,'-')-1 <= 0 then 100
      else instr(u.username,'-')-1 end)))

      order by 1

  22. Pat

    How did you get two customers on the same order number?

  23. david

    Hi Oscar,
    I have sql2005, have a table with many columns.
    I want to display several selected columns, but i want to get a distinct on one column and the most current record and excluding records that have and id that starts with rt.
    ie
    table trans
    id item date cust qty price
    1 c1 1/1//2011 ted 1 10
    2 c2 1/3/2011 ted 2 15
    3 c1 1/20/2011 ted 4 10
    rt2 c1 1/21/2011 ted 1 10

    i want to be able to

    get
    id item date cust qty price
    3 c1 1/20/2011 ted 4 10
    2 c2 1/3/2011 ted 2 15

    i know using distinct and max will do and was able get only item and date column only.

    Thank you
    David

  24. Roberto

    Hi Oscar,

    I use SQLServer 2008R2. I just would to say that you did a terrific job with that query you have proposed to Prada on March 8, 2011 at 5:52 am.
    It worked like a glove for me.
    It’s 3:11am and I was stuck with this problem as all my development was relying in such solution with distinct.

    God bless you!

  25. Fábio Pinho

    Hi.
    Well, I think I have a different problem, untill now I have this:

    SELECT DISTINCT Products.Name, Clients.Name
    FROM Products
    INNER JOIN Clients ON Clients.IDProduct = Products.ID
    ORDER BY Products.Name

    The point is: I have two colums, the main column is Products, which can contains for example: Fish, Vine – and are several clients for this products, so I would like to retrieve it like this way:

    Fish
    Client 1
    Client 2
    Client 3
    Vine
    Client 1
    Client 2
    Client 3

    I tried your example and it didn’t work. I’m using MySQL. Maybe I’m doing something wrong? Thanks.

    • It seems like the solution to your problem would be answered by with reporting software. Oracle may accomplish what you are looking to do with Grouping Sets, but I don’t know that MySQL can do that for you. The closest answer I can give you is to use ROLL UP. I added a client count to the query below, but you can omit it if that’s not of interest to you.

      SELECT Products.Name, Clients.Name, Count(Clients.Name) ‘Client Count’
      FROM Products, Clients
      WHERE Clients.IDProduct = Products.ID
      GROUP BY Products.Name, Clients.Name WITH ROLLUP

  26. Maria

    Hi,
    Can anyone please help me? I am new to SQL.
    I have a table with some data of which two columns are id(varchar2) and name.
    MarEl 5022
    MarEl 5022
    MarEl 5022
    Maxim W 2609
    Maxim W 5860
    I need to select distinct id and the names associated with it. I tried many of the ideas explained here but didnt work for me. I am using oracle.

  27. tanya romero

    thanks a lot!

  28. pete

    hi there,

    I have a problem. I’ve got a table which is as follows.

    http://i39.tinypic.com/4q6gz4.png

    I need to display just the supervisor and the name column of only the staff who are supervisors. So the result should contain 6 of them.

    So far have

    SELECT DISTINCT supervisor
    FROM employee
    WHERE supervisor IS NOT NULL

    This returns the numbers of the supervisors, but we are unsure of how to pull out only the names of these listed supervisors.

    Any help would be greatly appreciated.

    • Hi Pete,
      If I am understanding your data correctly, the field supervisor contains the employee id of that individual’s supervisor. If that’s the case, one way to only list the names of employees who are supervisors over others is:

      SELECT DISTINCT name
      FROM employee
      WHERE emp_id IN (SELECT DISTINCT supervisor FROM employee)

      If this solution does not work, please provide additional info about your data. Also, the only position that came up six times for your employees is “Manager”; Assuming the table pictured is inclusive of all your data, if Manager is synonymous with supervisor, you can do a select query like the one above except your WHERE clause would look like:

      WHERE position = ‘Manager’

  29. Nicholaus

    I am also having trouble, and would love to get some help. I’m doing a little more difficult of a query w/multiple joins, but I would think I could still get one column to be distinct. My unique ID column from the main table is ocReferralID – and my query is as follows:

    SELECT OC.*, J.[FirstName] AS [FirstNameJud], J.[LastName] AS [LastNameJud], J.[ACCI], A.[Name] AS [ocAgencyName]
    FROM [ocReferral] AS OC
    INNER JOIN [ocJudicialUser] AS J ON OC.[ocJudicialUserID] = J.[ocJudicialUserID]
    INNER JOIN [ocReferralActionNotes] AS RAN ON OC.[ocReferralID] = RAN.[ocReferralID]
    RIGHT JOIN
    (
    SELECT DISTINCT [ocReferralID] FROM [ocReferral]
    ) AS DR ON DR.[ocReferralID] = OC.[ocReferralID]
    LEFT OUTER JOIN [ocAgency] AS A ON J.[ocAgencyID] = A.[ocAgencyID]
    WHERE 1=1
    AND RAN.[ActionName] LIKE ‘%Compl%’

    So I’m getting all of my results and that’s great, but I am NOT getting distinct on my ocReferralID column – so probably 1/3 to 1/2 of my results are all duplicates… Any ideas on this one? I have been working on it for hours and your post is the first one that seems relevant and helpful. I would really appreciate if you could offer any of your experience/help on this one :)

    • I wish I could see the query results or at least have some sample data to ensure the following solution will produce what you need. But since I can’t I am making the following assumptions:

      There are rows in your ocReferral table where it doesn’t necessarily matter which ones are selected, as long as they provide proof that a record was created.
      You are more concerned with capturing names, ACCI and agency and that some relationship exists between that info and fields in the ocReferral.
      When you note that you have duplicate ocReferralIDs, that also means you have duplicate names, ACCIs and Agencies in those duplicate records

      With that said you can view my proposed solution here: It consists of adding a function over partition in the first select clause and encapsulating your query results and making it a derived tablehttp://www.box.net/s/549orq9uqgdn42nn1qxk

      • Nicholaus

        @oscarvalles, from what I understand, row_number() would be awesome to use, but I can’t, since this isn’t SQL 2005 – I’m working w/2000 on this database. Any other ideas?

      • Nicholaus

        @oscarvalles, I figured it out. I changed my query to this instead:

        SELECT OC.*, J.[FirstName] AS [FirstNameJud], J.[LastName] AS [LastNameJud], J.[ACCI], A.[Name] AS [ocAgencyName]
        FROM [ocReferral] AS OC
        INNER JOIN [ocJudicialUser] AS J ON OC.[ocJudicialUserID] = J.[ocJudicialUserID]
        LEFT OUTER JOIN [ocAgency] AS A ON J.[ocAgencyID] = A.[ocAgencyID]
        WHERE
        (
        SELECT COUNT(*) FROM [ocReferralActionNotes]
        WHERE [ocReferralID] = OC.[ocReferralID]
        AND [ActionName] LIKE ‘%compl%’
        ) > 1

        That way it allows it to be unique, and everything works. Just thought I’d let you know. Thanks for trying to help :)

      • Glad to hear it. It sure beats the solution I worked out. http://www.box.net/s/fci82f8s069176o172ct Thanks for the update and sharing your code.

  30. AKM

    Hello,

    I have been having this problem for a whil enow and can’t seem to implement it with my limited knowledge into my query string.

    I also have an inner join but didn’t know how to implement it within it. Here is what I have:

    SELECT customer.ID, customer.FirstName, customer.LastName, customer.Male, customer.Female, customer.Phone, customer.Followup, tblservice.Timing, tblservice.Service1, tblservice.Total FROM customer INNER JOIN tblservice ON customer.ID=tblservice.customerID WHERE customer.Followup=No;

    There are two tables here as you can note the primary and foreign keys os customer ID. How would I display the results with distinct customer.ID field?

    Any help would be great.

    • Hi AKM,
      Assuming the customer table’s ID column has unique values, meaning they only show up once in the table, then the simplest way to do it is by
      1.) Replace “FROM customer” with “FROM tblservice”
      2.) Replace “INNER JOIN tblservice” with “RIGHT JOIN customer”

      If you have duplicates in both, feel free to send me a sample of the data or try using the technique described here: http://www.box.net/shared/y6boje6fhhv30y8jsrrh assuming you are using SQL Server 2005 or greater.

  31. Heave thx for mysql version ;)

  32. Steve

    Thanks, your solution to Prada helped me out greatly.
    (SQL Server 2008 R2)

  33. Kevin

    Any ideas on how to do an SQL Distinct on one column only when you are also joining multiple tables together? Thanks

    • Assuming you are using SQL Server 2005 or later, then here is an example: https://www.box.com/s/a8v61c8x8a1x61k6pa8b
      Be sure to read the notes at the top. It will help explain the query and provide you with a performance tip.

      If you are not using SQL Server 2005 or later, then please submit your table and field names and identify which field(s) you wish to make the record unique and what fields you are joining your tables on.

  34. Pingback: sql select only one column distinct? | Askjis

Comment on this

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s