HAVING a problem

Hi NG,
I have a problem with a query. I have three columns with relations from
A to B and the number of eg. Orders:
C1 | C2 | Number
A B 17
A C 4
A E 23
B A 22
B G 19
B J 21
What I want is to get from each C1 element (A, B) the relation to the
C2 with the lowest number, in this example:
C1 | C2 | Number
A C 4
B G 19
How can I do this?
Thank you very much,
RudiSELECT c1, c2, number
FROM your_table AS T
WHERE number =
(SELECT MIN(number)
FROM your_table
WHERE c1 = T.c1) ;
David Portas
SQL Server MVP
--

Handle 1:n relations

Hi,

I build a local cube from a relation database. In the database there are 1:n relations.
Is there a way to handle 1:n relations?
For example:
I have a table LOGGEDFLAW and a table LOGGEDREASON with a 1:n relation between them. We create a select statement of these tables and as an result we get duplicate records of LOGGEDFLAW each time more than 1 record of LOGGEDREASON are associated to 1 record of LOGGEDFLAW - this is the standard result I get with an relational JOIN operation. Now I want to count the LOGGEDFLAWs without the duplicates generated by the 1:n relationship.

Best regards,
ThorstenUSE Northwind
GO

CREATE VIEW myView99
AS
SELECT o.OrderId, od.Quantity
FROM Orders o INNER JOIN [Order Details] od
ON o.OrderId = od.OrderId
GO

SELECT COUNT(DISTINCT OrderId), COUNT(*)
FROM myView99
GO

DROP VIEW myView99
GO|||Oh sorry, I create a local cube. I need a way to create distict measures.