Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tryharder
Starting Member
17 Posts |
 Posted - 2014-10-31 : 09:34:34
|
| Hi,I'm try to do the following but not working please help!!‘Trying to SELECT INTO a new table all columns of a table based on a DISTINCT value of one column so for example:SELECT * INTO new_tableFROM old_nameWHERE old_table.column IS DISTINCT’thanksSP |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-10-31 : 10:20:20
|
| Assuming you are using Microsoft SQL Server, the syntax is not correct. If you are looking for only distinct values of one column, what rule do you want to determine which values for other columns to use? For example, consider that you have a table with two columns: col1 and col2. Assume that you have only two rows in the table. (1,10), and (1,11). Now, if you want to use distinct values of col1, there is only one distinct value of col1, so you will insert only one row into your new table. What value do you want to use for col2 then - 10, or 11? |
 |
|
|
tryharder
Starting Member
17 Posts |
Posted - 2014-10-31 : 10:30:58
|
| Hi James thanks for the quick reply.Essentially what I'm after is the following:I have a table that looks like this| id | title | forename | surname || 1 | Mr | Joe | Blogs || 2 | Mrs | Joe | Blogs || 1 | Mr | Joe | Blogs || 3 | Miss | Sara | Smith |I want to extract only one instance of each row and put them into a new table so that the new table is:| id | title | forename | surname || 1 | Mr | Joe | Blogs || 2 | Mrs | Joe | Blogs || 3 | Miss | Sara | Smith |Containing no duplicates.Sorry if I didn't explain myself clearly.ThanksSP |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-10-31 : 11:25:21
|
| [code]SELECT DISTINCT * INTO new_table FROM old_name[/code] |
|
|
|
tryharder
Starting Member
17 Posts |
Posted - 2014-10-31 : 11:32:53
|
| Many thanks JamesSP |
|
|
|
|
|
|
DISTINCT Query