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 |
|
goligol
Posting Yak Master
128 Posts |
 Posted - 2011-11-29 : 13:27:55
|
| Hi,I would like to create a view of the following table where it keeps only one record only if "port is duplicating:port port_name type01 PORTLAND MAINE District01 PORTLAND, ME 0101 Portland, Maine Port0101 PORTLAND, ME Port0102 Bangor, Maine Port0102 BANGOR, ME Port0103 Eastport, Maine Port0103 EASTPORT, ME Port0104 JACKMAN, ME Port0104 USA US 201/HWY 6 JACKMAN//CAN HWY 173 ARMSTRONG Port0105 Vanceboro, Maine Port |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-11-29 : 13:48:05
|
Probably the easiest way is to use row_number function like this. You can use row_number function in views:WITH cte AS( SELECT *, ROW_NUMBER() OVER (PARTITION BY port ORDER BY (SELECT NULL)) AS RN FROM YourTable)SELECT port, port_name, [type] FROM cte WHERE RN=1; |
 |
|
|
goligol
Posting Yak Master
128 Posts |
Posted - 2011-11-29 : 14:13:00
|
| Wow, that works, thank you |
|
|
|
|
|
|
|
|
keep one record of duplicated record