p*p
发帖数: 80 | 1 Could someone help me with the following query? Say there is a table with 2
columns, ProductID and ProductType
ProductID ProductType
1 C
2 B
3 A
4 B
5 A
6 C
7 A
8 B
how to write a query to return 2 products (order by ProductID) from each
producttype? In this case, return:
3 A
5 A
2 B
4 B
1 C
6 C
Use Transact-SQL, one query, no cursor is allowed.
Please help!!!! |
c**t
发帖数: 2744 | 2 select * from table
where productID in (select distinct productID from table
having count(ProductType) = 2
)
order by productType
【在 p*p 的大作中提到】
: Could someone help me with the following query? Say there is a table with 2
: columns, ProductID and ProductType
: ProductID ProductType
: 1 C
: 2 B
: 3 A
: 4 B
: 5 A
: 6 C
: 7 A
|
j**i
发帖数: 419 | 3 Looks like this is not what he means, he wants
everything be returned twice, even count(*) may >2,
right?
in oracle you can use rownum to do this bah.
2
【在 c**t 的大作中提到】
: select * from table
: where productID in (select distinct productID from table
: having count(ProductType) = 2
: )
: order by productType
|
k***e
发帖数: 12 | 4 select p.* from product p
where p.productid in (select top 2 productid from product where
producttype=p.producttype)
order by producttype,productid
with
【在 j**i 的大作中提到】
: Looks like this is not what he means, he wants
: everything be returned twice, even count(*) may >2,
: right?
: in oracle you can use rownum to do this bah.
:
: 2
|
j**i
发帖数: 419 | 5
This is not good in oracle bah.
which do you use?miscrosoft sql server?
each
【在 k***e 的大作中提到】
: select p.* from product p
: where p.productid in (select top 2 productid from product where
: producttype=p.producttype)
: order by producttype,productid
:
: with
|
