If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Select Distinct ..." or something else?
Hi:
I have a table that has an auto number field as the key, and two columns (say col1 and col2) that will have duplicated rows and other columns that is quite unique. I need select all columns, but I don't want to have duplicated col1 and col2. Let me put the Table1 as following: No. col1 col2 col3 col4 col5 1 abc ttc1 789 234 235 2 abc ttc1 688 375 890 3 bdc yyr2 797 378 379 4 bdc yyr2 794 375 235 5 bdc yyr2 789 365 459 I need only rows 1 and 3 with all values from col1 to col5 abc ttc1 789 234 235 bdc yyr2 797 378 379 or rows 1 and 4 or rows 2 and 4, ... only requirement is col1 and col2 are distinct. If I put: Select distinct col1, col2, col3, col4, col5 From Table1 I will got all rows, which is not what I want. Actually, I cannot use Where clause, as there are in fact thousands of rows with several hundreds of distinct rows that have unique col1 and col2 values. How can do this? |
#2
|
|||
|
|||
Why values from row 1 and 3 and not 2 and 5?
-- Duane Hookom MS Access MVP "Min" wrote in message ... Hi: I have a table that has an auto number field as the key, and two columns (say col1 and col2) that will have duplicated rows and other columns that is quite unique. I need select all columns, but I don't want to have duplicated col1 and col2. Let me put the Table1 as following: No. col1 col2 col3 col4 col5 1 abc ttc1 789 234 235 2 abc ttc1 688 375 890 3 bdc yyr2 797 378 379 4 bdc yyr2 794 375 235 5 bdc yyr2 789 365 459 I need only rows 1 and 3 with all values from col1 to col5 abc ttc1 789 234 235 bdc yyr2 797 378 379 or rows 1 and 4 or rows 2 and 4, ... only requirement is col1 and col2 are distinct. If I put: Select distinct col1, col2, col3, col4, col5 From Table1 I will got all rows, which is not what I want. Actually, I cannot use Where clause, as there are in fact thousands of rows with several hundreds of distinct rows that have unique col1 and col2 values. How can do this? |
#3
|
|||
|
|||
Thank you for your reply.
Acutally, there is no reason why should row 1 and 3 and but not 2 and 5, only requirement is col1 and col2 must be distinct. I've got the answer from Dirk Goldgar. Sorry for multipost. When I was told it is better to post query question on this group, I did both, just want to get early answer. Thank you anywhere. Min ------------------------------------------------------------------------ As long as you are dealing with Access databases and Jet SQL, you can use a query like this: SELECT col1, col2, First(col3) AS col3, First(col4) AS col4, First(col5) AS col5 FROM Table1 GROUP BY col1, col2; The First() function can't actually be guaranteed to return you the data from the first record in each group (though it likely will if your table has a primary key) -- but then, you say you don't actually care about that. -- Dirk Goldgar, MS Access MVP ------------------------------------------------------------ "Duane Hookom" дÈëÓʼþ ... Why values from row 1 and 3 and not 2 and 5? -- Duane Hookom MS Access MVP "Min" wrote in message ... Hi: I have a table that has an auto number field as the key, and two columns (say col1 and col2) that will have duplicated rows and other columns that is quite unique. I need select all columns, but I don't want to have duplicated col1 and col2. Let me put the Table1 as following: No. col1 col2 col3 col4 col5 1 abc ttc1 789 234 235 2 abc ttc1 688 375 890 3 bdc yyr2 797 378 379 4 bdc yyr2 794 375 235 5 bdc yyr2 789 365 459 I need only rows 1 and 3 with all values from col1 to col5 abc ttc1 789 234 235 bdc yyr2 797 378 379 or rows 1 and 4 or rows 2 and 4, ... only requirement is col1 and col2 are distinct. If I put: Select distinct col1, col2, col3, col4, col5 From Table1 I will got all rows, which is not what I want. Actually, I cannot use Where clause, as there are in fact thousands of rows with several hundreds of distinct rows that have unique col1 and col2 values. How can do this? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
select (distinct fieldname) in access ? | Paulo Nunes | Running & Setting Up Queries | 3 | August 31st, 2004 07:22 AM |
Do not Count Duplicates | bdehning | Running & Setting Up Queries | 25 | July 28th, 2004 05:47 AM |
select distinct on 1 field only | John Smith | General Discussion | 1 | June 24th, 2004 12:59 PM |
"Select Names" box to select email recipients | ahron | Contacts | 1 | April 26th, 2004 07:49 PM |
error in excel help. how to select a cell after clicking the Select All button? | Dmitriy Kopnichev | Links and Linking | 3 | December 5th, 2003 10:32 PM |