0

I am trying to remove certain rows based on unique values within the table and still display the other column data associated with the row.

For example:

x------------------------------------------------ x
x Process ID  Name         Address    Random Data x
x------------------------------------------------ x
x 123        TestName     TestAdd    qwrj3ri      x
x 456        TestName2    TestAdd    qwerty       x
x 789        TestName     TestAdd    qwrj3ri      x
x 1234       Testing123   tester     asdfghjk     x
---------------------------------------------------

In access, after importing the table from excel.

I used this query:

SELECT DISTINCT Test.Name, Test.Address
FROM Test;

The problem is that while it displays the Unique Name and Address, I also would like to display the Process ID, and Random Data column that is associated with the Unique Name, and Address. What I am trying to do is remove duplicate based on the name and address and still display the relevant data for the unique value/row.

Bulat
  • 6,869
  • 1
  • 29
  • 52
syavatkar
  • 85
  • 1
  • 1
  • 7
  • Maybe you could provide a sample output? – FutbolFan Jul 30 '15 at 20:36
  • why do you have mysql tag? – Bulat Jul 30 '15 at 20:39
  • possible duplicate of [Select multiple (non-aggregate function) columns with GROUP BY](http://stackoverflow.com/questions/17411177/select-multiple-non-aggregate-function-columns-with-group-by) – Bulat Jul 30 '15 at 20:43
  • well I am trying to keep the ID as it will be used to search an existing system for the given entitiy. There are two rows with the same name and address. I want to remove one of them and keep the other. The duplicates are decided by the name and address. When I use the distinct query it returns the unique rows withouth the ID being displayed and the other columns – syavatkar Jul 30 '15 at 20:46
  • @user3341712 Okay! How do you determine which `Process ID` and `Random Data` to pull? It will return multiple records if we join back on `Name` or `Address`. – FutbolFan Jul 30 '15 at 20:48
  • @FutbolFan For me all sql - mysql :) – splash58 Jul 30 '15 at 21:01
  • @splash58 Same goes for me all sql = SQL Server. :) – FutbolFan Jul 30 '15 at 21:02
  • 1
    @FutbolFan I want it to pull the first process ID associated with the name. I just included Random Data to represent the other columns that I currently have. Basically if I have two process IDs 123 and 789. I want it to pull 789 for TestName. Any idea how can pull the data for the other columns associated with the entity. So , taking a look at the table I included above. I want it to display the first instance of the having the same name and address which in this case is TestName with the Process ID 123. – syavatkar Jul 30 '15 at 21:04
  • 1
    @Bulat The method you sugested deals with the arranging max, min values. – syavatkar Jul 30 '15 at 21:06
  • @user3341712 without more details on the desired output that method seems to do what you need. – Bulat Jul 30 '15 at 21:09
  • @Bulat http://www.sqlfiddle.com/#!9/eaf9b/1 . Here is the output I get after running the distinct query. However, I am trying to also display the ID, and other columns with that are associated with the unique entity. – syavatkar Jul 30 '15 at 21:27

1 Answers1

0

Not sure why you don't agree that Select multiple (non-aggregate function) columns with GROUP BY provides the answer:

SELECT t.ProcessID, t.Name, t.Address, RandomData
FROM Test t
INNER JOIN (
SELECT c.Name,c.Address, MIN(c.ProcessID) ProcessID
FROM test c
GROUP by c.Name, c.Address
) ss ON t.Name = ss.Name AND t.Address= ss.Address
AND t.ProcessID = ss.ProcessID

http://www.sqlfiddle.com/#!9/eaf9b/6

Community
  • 1
  • 1
Bulat
  • 6,869
  • 1
  • 29
  • 52
  • I am a bit confused..Why did you select distinct process id and random data? I am trying to find duplicates by two criteria which is the name and address. From running your code, it seems to provide me the values that I am looking for. But, trying to understand the syntax of the code. – syavatkar Jul 31 '15 at 13:26
  • what is the puspose of using T within your insert statement? Access keeps displaying a missing semicolon at the end of the statement error and there seems to be nothing wrong with the code you provided. When I ran it in sqlfiddle, it seems fine. But, Access acts differently – syavatkar Jul 31 '15 at 14:38
  • You don't need distinct indeed – Bulat Jul 31 '15 at 14:57
  • I am not sure why it does not work in MS Access. sqlfiddle is working in MySQL which can be different. Actually there is no T in my sqlfiddle – Bulat Jul 31 '15 at 15:00