Need Help with Query
-
Hi,
I am sure that this is easy but I can't make it. Here is the sample data:
create table #Table
(ID int,
Owner varchar(100),
PropertyID int)
insert #Table values (1,'A', 100)
insert #Table values (2,'A', 101)
insert #Table values (3,'C', 102)
insert #Table values (4,'D', 103)
insert #Table values (5,'E', 104)
insert #Table values (6,'F', 104)
insert #Table values (11,'A', 100)
insert #Table values (12,'A', 101)
insert #Table values (13,'C', 102)
insert #Table values (14,'D', 103)
insert #Table values (15,'E', 104)
insert #Table values (16,'F', 104)
select * from #Table order by Owner, PropertyID
Each owner has two entries for each property he owns in the table. Owner A has 2 rows for property 100 and 2 rows for property 101. Owner C has 2 rows for property 102. Same for owner D with property 103. Owner E and F co-own property 104; each has 2 rows for property 104.
I'd like to have a query that will show only one row for each owner for each property he owns. The value of ID can be either the greater one or smaller one; it doesn't matter. I know that I can use MAX(ID) or MIN(ID) in the query but its just running away from me.
Thanks.
-
or this
SELECT Owner,
MIN(PropertyId) AS MinId,
MAX(PropertyId) AS MaxId
FROM #Table
GROUP BY Owner
-
Looking for this query.
SELECT DISTINCT Owner,PropertyId FROM #Table
-
I came up with this one:
select * from #Table
where id not in
(select min(id) from #Table
group by Owner, PropertyID)
Thanks anyway.
-
Nice, but if you ever end up with 3 entries for a property, your method won't work.
But, this will...
SELECT MAX(ID) AS ID,Owner,PropertyID
FROM #Table
GROUP BY Owner,PropertyID
My real question is that if you really don't care which ID you use, why do you need it in the return at all. What do you intend to use if for?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You are correct. Actually this is just a sample I made up to represent a complicated table with data collected from reading devices and change every minutes. Thanks.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply