How to get unique rows with getmulti

Posts   
 
    
Pablo
User
Posts: 81
Joined: 21-Mar-2005
# Posted on: 13-Mar-2012 15:27:50   

Hello, I've run into a 'challenge' I don't know how to solve, hopefully someone can provide help!

I've got table A with 4 fields F1, F2, F3, F4

I'd like to get a collection of rows, - each row containing all 4 fields. - containing max n rows. - Without rows with duplicate values in field F3 and F4. - sorted on the datetime field.

F1 is a PK id.-> unique F2 is a 'datetime field' -> probably unique F3 is a varchar(20) -> not unique F4 is a varchar(30) -> not unique

Example: Row 1: 1 2012-01-01 f3text1 f4text1 Row 2: 2 2012-01-04 f3text2 f4text2 Row 3: 3 2012-01-08 f3text2 f4text3 Row 4: 4 2012-01-02 f3text2 f4text2 Row 5: 5 2012-01-03 f3text3 f4text5

Desired result with MaxRows=4: Row 1: 1 2012-01-01 f3text1 f4text1 Row 4: 4 2012-01-02 f3text2 f4text2 Row 5: 5 2012-01-03 f3text3 f4text5 Row 3: 3 2012-01-08 f3text2 f4text3

Desired result with MaxRows = 3: Row 1: 1 2012-01-01 f3text1 f4text1 Row 4: 4 2012-01-02 f3text2 f4text2 Row 5: 5 2012-01-03 f3text3 f4text5

Thanks in advance for any help!

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 13-Mar-2012 21:11:17   

I don't understand the part where you say "Without rows with duplicate values in field F3 and F4", because in your 'Desired results' you have 'f3text2' value twice.

David Elizondo | LLBLGen Support Team
Pablo
User
Posts: 81
Joined: 21-Mar-2005
# Posted on: 13-Mar-2012 21:32:57   

I'd like to get the results with all combinations of F3 and F4 but not duplicate combinations.

In the example I got f3text2 twice, but each time with a different value in F4.

Selfservice, 2.6 ...

How to do it in SQL?? I come close using a straighforward GROUP BY, but then I can't retrieve F1 and F2 ...

Maybe I can work around the fact that the query does not produce F1 and F2.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 14-Mar-2012 06:48:36   

I will try to explain this with similar example. Lets suppose we want ALL Orders which their date match with the minimum for all the possible unique combinations of ShipCountry and ShipDate.

In your words, we want this: - each row containing all fields. - containing max n rows. - Without rows with duplicate values in field ShipCity and ShipCountry. - sorted on the OrderDate field.

This would be an approximate SQL for that request:

SELECT O.* 
FROM Orders O
JOIN
   (SELECT MIN(OrderDate) AS OrderDate FROM Orders O2
    GROUP BY ShipCity, ShipCountry
   ) O2 ON O.OrderDate = O2.OrderDate
ORDER BY O.OrderDate

To do that, you could use DerivedTable and DynamicRelation. This is the C# code for that (using SelfServicing):

// First define the fields of the derived table select
ResultsetFields dtFields = new ResultsetFields(1);
dtFields.DefineField(OrderFields.OrderDate, 0, AggregateFunction.Min);

// now define the grouper that will be inside the devied table
var grouper = new GroupByCollection();
grouper.Add(OrderFields.ShipCountry);
grouper.Add(OrderFields.ShipCity);

// define the derived table
DerivedTableDefinition dtDefinition = new DerivedTableDefinition(
                dtFields, "O2", null, grouper);



// specify the relation which is a dynamic relation. 
var relation = new DynamicRelation(dtDefinition, JoinHint.Inner,
                                EntityType.OrderEntity, "O",
                                (new EntityField(OrderFieldIndex.OrderDate.ToString(), "O2", typeof(DateTime)) ==
                                OrderFields.OrderDate.SetObjectAlias("O")));


// add the dynamic relation to the relations that will be used in the main fetch
RelationCollection relations = new RelationCollection();
relations.Add(relation);
relations.SelectListAlias = "O";  // entities should target the aliased order table. 

// sorter
var sorter = new SortExpression(OrderFields.OrderDate.SetObjectAlias("O") | SortOperator.Ascending);

// fetch the data
var maxRows = 10;
OrderCollection orders = new OrderCollection();
orders.GetMulti(null, maxRows, sorter, relations);

.. and this is the final generated SQL for above code:

SELECT DISTINCT TOP 10
    [LPA_O2].[OrderID] AS [OrderId], 
    [LPA_O2].[CustomerID] AS [CustomerId], 
    [LPA_O2].[EmployeeID] AS [EmployeeId], 
    [LPA_O2].[OrderDate], 
    [LPA_O2].[RequiredDate], 
    [LPA_O2].[ShippedDate], 
    [LPA_O2].[ShipVia], 
    [LPA_O2].[Freight], 
    [LPA_O2].[ShipName], 
    [LPA_O2].[ShipAddress], 
    [LPA_O2].[ShipCity], 
    [LPA_O2].[ShipRegion], 
    [LPA_O2].[ShipPostalCode], 
    [LPA_O2].[ShipCountry] 

FROM 
    ( 
        ( SELECT MIN([Northwind].[dbo].[Orders].[OrderDate]) AS [OrderDate] 
            FROM [Northwind].[dbo].[Orders]  
            GROUP BY [Northwind].[dbo].[Orders].[ShipCountry], [Northwind].[dbo].[Orders].[ShipCity]
        ) [LPA_O1]  
        
        INNER JOIN [Northwind].[dbo].[Orders] [LPA_O2]  ON  [LPA_O1].[OrderDate] = [LPA_O2].[OrderDate]
    ) 
    
ORDER BY [LPA_O2].[OrderDate] ASC

... which is exactly what we want. Now try it with your own scenario. Buena suerte wink

David Elizondo | LLBLGen Support Team
Pablo
User
Posts: 81
Joined: 21-Mar-2005
# Posted on: 14-Mar-2012 11:21:57   

Daelmo, thanx for your help. I've been able to put this to work, estupendo!

By reading your solution, I realised that by using the aggregate function on a field it is possible to use that field in the ORDERBY clause as well even though it is not in the GROUPBY clause (i use that now on the datetime field).

Thanx!!

Paul