Selecting only two rows with the same attributes

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

SQL Server 2000 Forums

Transact-SQL (2000)

Selecting only two rows with the same attributes

Author

Topic

Session101
Starting Member

15 Posts

Posted - 2006-07-31 : 12:17:24

I currently have a query that will display two entries if they have the same attributes (namely, quantity and price). Here is my current query:

select id, quantity, price
from store t
join store c
on t.quantity = c.quantity
and t.price = c.price

My problem is that I only want the query to display only 2 entries withthe same price and quantity. If a third entry apears with the same attributes, I do not want it to be displayed. I have been trying to use the function count, but I can't seem to get it to work.

TIA.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-31 : 12:24:12

select t.id, t.quantity, t.price
from store t
join (select minid = min(id), maxid = max(id) from store group by quantity, price having count(*) > 1) t2
on t.id = t2.minid
or t.id = t2.maxid

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources