SQL Distinct
-
So I have a database with multiple records with the same agtMast field. What I need to do is select unique agtMast records, but i need the whole record. I know this will select the unique fields... SELECT DISTINCT agtMast FROM table_name; ...but it only selects the agtMast field and I need the entire record. Any help would be greatly appreciated. Nathan Lindley
-
So I have a database with multiple records with the same agtMast field. What I need to do is select unique agtMast records, but i need the whole record. I know this will select the unique fields... SELECT DISTINCT agtMast FROM table_name; ...but it only selects the agtMast field and I need the entire record. Any help would be greatly appreciated. Nathan Lindley
Well, say there are 3 rows agtMast ID Name Other One 22 James Other1 One 22 James Other2 Two 22 Bond Whee! There are 2 distinct values for the agtMast column, but 2 possible sets of values are different. Do you want one row for each agtMast value, and are there specifics as to which one should be selected, or do you want the distinct sets of values (So if One/22/James/Other1 appeared 10 times, you would want it to appear only once, but have One/22/James/Other2 appear as well after the query) I need some more information as to how you're clarifying the "correct" row.
-
So I have a database with multiple records with the same agtMast field. What I need to do is select unique agtMast records, but i need the whole record. I know this will select the unique fields... SELECT DISTINCT agtMast FROM table_name; ...but it only selects the agtMast field and I need the entire record. Any help would be greatly appreciated. Nathan Lindley
-
Well, say there are 3 rows agtMast ID Name Other One 22 James Other1 One 22 James Other2 Two 22 Bond Whee! There are 2 distinct values for the agtMast column, but 2 possible sets of values are different. Do you want one row for each agtMast value, and are there specifics as to which one should be selected, or do you want the distinct sets of values (So if One/22/James/Other1 appeared 10 times, you would want it to appear only once, but have One/22/James/Other2 appear as well after the query) I need some more information as to how you're clarifying the "correct" row.
Thanks for the reply Drew. What I am looking for is just one of each of the agtMast (which is the agent's code) records. What the situation is is before on the old system, they listed each agent every time they changed an address (instead of updating, they just added new rows). What I need to do is just get one of the records (1 for each agtMast code, but no more than 1), for the main contact information and if the address is not the current one, it doesn't matter. I would just put the DISTINCT records into a holding table, but i'm not able to grab just a single record for each DISTINCT agtMast. Thanks again for the reply! Nathan Lindley
-
Thanks for the reply Ed. How can I use group by agtMast if the select includes more columns than the agtMast? Nathan Lindley
-
Well, you can't include a column in the select statement without it being contained in either an aggregate function or containing it in the GROUP BY clause. Nathan Lindley
Exactly. I must be missing something on your requirement. You want one record per agtMast, and you want the other columns also. You have to determine what you want from the other columns. For example: SELECT agtMast, MAX(CustName), MAX(DateIssued).... FROM table_name;
-
Exactly. I must be missing something on your requirement. You want one record per agtMast, and you want the other columns also. You have to determine what you want from the other columns. For example: SELECT agtMast, MAX(CustName), MAX(DateIssued).... FROM table_name;
Yea, I used MIN(), but what I essentially did was MIN(column1) As Column1, etc.. and inserted the records into a temp table, cleared the original table, and then loaded the records back into the original table. I don't know why I didn't do this in the first place, but i guess i was looking for a more concrete way to do it. Oh well, this works. Thanks again for the responses! Nathan Lindley