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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 DISTINCT Query

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_table
FROM old_name
WHERE old_table.column IS DISTINCT’


thanks

SP

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?
Go to Top of Page

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.

Thanks

SP
Go to Top of Page

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]
Go to Top of Page

tryharder
Starting Member

17 Posts

Posted - 2014-10-31 : 11:32:53
Many thanks James

SP
Go to Top of Page
   

- Advertisement -