March 7, 2011 at 2:41 pm
I have a temporary table I created from joins of 3 other tables ordered by PortNumber, INumber and Name. The results of the table similar to:
INumber Account Name PortNumber
1 123 1 100
2 234 1 200
2 345 1 200
2 456 2 200
1 567 1 101
1 678 1 101
What I want to ultimately get from this table are unique INumber, Name and PortNumber but also include the Account column. So I want to delete the rows where there is already a INumber, Name and PortNumber in the table. Like this:
INumber Account Name PortNumber
1 123 1 100
2 234 1 200
2 456 2 200
1 567 1 101
How can I write code to get from table 1 to table 2? Is it possible?
....I am new to T-SQL and trying my hardest.
Thanks
March 7, 2011 at 2:45 pm
Depending on the data types, you can either do this with a CTE and Row_Number trick, or with Min() and Group By.
What are the data types of the columns?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 7, 2011 at 2:47 pm
All the columns are INT
March 7, 2011 at 2:50 pm
Try something like this:
select INumber, Name, PortNumber, min(Account) as MinAccount
from MyTable -- put your table name here, obviously
group by INumber, Name, PortNumber;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 7, 2011 at 2:55 pm
YOU are a genious!
THANK YOU!
March 8, 2011 at 6:14 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply