Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> How to get unique rows with getmulti
 

Pages: 1
Bugs & Issues
How to get unique rows with getmulti
Page:1/1 

  Print all messages in this thread  
Poster Message
Pablo
User



Location:
Sneek
Joined on:
21-Mar-2005 01:17:52
Posted:
81 posts
# 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!




  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8060 posts
# 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.

- How would you do that in SQL?
- What LLBLGen version are you using?
- Adapter or SelfServicing?
(http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717)


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Pablo
User



Location:
Sneek
Joined on:
21-Mar-2005 01:17:52
Posted:
81 posts
# 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.
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8060 posts
# 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:
Code:
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):
Code:
// 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:
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'ing (articles and code snippets) | linkedin | twitter
 
Top
Pablo
User



Location:
Sneek
Joined on:
21-Mar-2005 01:17:52
Posted:
81 posts
# 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
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.