SQL quiries to select attribute base on sequence

[dhen21dx]

Hello,

Need help on ms access sql, i need to select records base on sequence but i only need to retain 1 line. Sequence are Orange, Lychee, Apple the Mango.


Data

Field 1	Field 2
Item 1	Apple
Item 1	Orange
Item 1	Mango
Item 1	Lychee
Item 2	Apple
Item 3	Apple
Item 4	Lychee
Item 4	Apple
Item 5	Lychee
Item 6	Mango
Item 6	Apple

Result:

Field 1	Field 2
Item 1	Orange
Item 2	Apple
Item 3	Apple
Item 4	Lychee
Item 5	Lychee
Item 6	Apple

 

[Micron]

If you do not have a way to sort what you show as Field2 items then I'd say that your only option is a code procedure. Without the sort field there is no way to use a domain function such as DMax or Max. In other words, if you had a table like

ItemID	ItemOrder	ItemName
1	5	Orange
2	10	Apple
3	15	Lychee
4	20	Mango

you could find the max ItemOrder value for a list of items. However, the example I show is based on what you wrote (Apple then Mango) whereas your sample data is the opposite. The reason that the sort order is in multiples of 5 is so that you can slip in other items between 15 and 20 if need be, and they don't have to be in ItemID order or even in ItemOrder order in your records. You'd structure your query to sort by ItemOrder ascending if you need to. So for a set of records that looks like your sample data, you'd query to get the Max ItemOrder value for each ItemName.

 

[dhen21dx]

Hi Micron,


Thanks, i think i better insert a field to identify the sorting. So it means if i used max, i will likely to get the 5 as orange for item 1? and so on? will this carried through queries or need a sql statement? Thanks for help, appreciate it a lot.

 

[Micron]

Not sure I understand the meaning of your statement about "sequence". My guess was that you wanted the latter value to be used instead of the "earlier" one in your sequence. Max would give you that, but if I have the goal wrong, then maybe you want the Min. Then again, which you use also depends on the order you have assigned to them. You ought to be able to write a query to get the results, which is really a sql statement - it's just stored as a query so not sure what that question means. A sql statement executed in code should produce the same result as a query. Unless you have some requirement to use coded sql, I don't see why you wouldn't just have a query as it's easier to maintain.

 

[xenou]

Using Micron's table for setting up a sort order, this query is what I came up with:

SELECT 
    t3.Field1, t4.ItemName 
FROM    
    (
    SELECT t1.Field1, Min(t2.ItemOrder) AS MinOfItemOrder
    FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.Field2 = t2.ItemName
    GROUP BY t1.Field1
    ) as t3
INNER JOIN 
    Table2 t4
    ON t3.MinOfItemOrder = t4.ItemOrder 
ORDER BY
    t3.Field1
;

 

[dhen21dx]

Hello,

Yeah, what i mean is that i will create new table to define the sequence of the Field 2 and indicated 5,10,15, 20 and so.. Thank you very much.