How To Select Only The Latest Rows from a Table

[PRACHI VAKHARIA]

How To Select Only The Latest Rows from a Table

For a Table structure as below:

Table(

PrimaryKey,
FieldName1,
FieldName2,
Time)

FieldName1 and FieldName2 are not unique.

Goal: To select only the latest row for each FieldName2

Items = db(db.Table.FieldName1 != Value1).select(orderby=~db.Table.Time, limitby=limitby, distinct=db.Table.FieldName2)

How to write the above statement so that it selects All the rows from the Table such that:

– For any given condition which here is: db.Table.FieldName1 != Value1 (works)
– Select All Rows from Table (works)
– Order those rows by Time field (works)
– limit selection by limitby number (works)
– And Selects only the Latest row for FieldName2 : Latest measured by Time ( distinct=db.Table.FieldName2  DOES NOT WORK)

Thank you, very much for reading. Only the part of selecting the distinct last element for FieldName2 does not work and I cannot figure it out. I hope someone can help.



—

[sesenmaister]

What does mean "DOES NOT WORK? Are you getting an OperationalError?

Use the argument groupby instead of distinct, and you'll get the desired results.

[Niphlod]

in addition to groupby you need to select the aggregate you want. 

"the latest row for fieldname2" is really the max(some_other_column) for each group.

That being said, please show your data and your desired resultset: I'm guessing it'll be a little harder to retrieve what you want.

[PRACHI VAKHARIA]

Please Refer to the Attached Image for the Table and DataSet types.

The output should be: The latest rows corresponding to each FieldName2 value.
Latest means Max(Time) for that particular FieldName2 value.

Output must be (Ref attached image):

1005

0020

F1_Values_0006

FN2_Value_1

1011

0032

F1_Values_0012

FN2_Value_2

1018

0046

F1_Values_0019

FN2_Value_3

These are the latest rows corresponding to each FieldName2 values: FN2_Value_1, FN2_Value_2, FN2_Value_3
For the latest Times corresponding to each of the FieldName2 values.


How to write DAL Query to fetch these?



Thank you, very much!

— PRACHI —

[Niphlod]

so group by fieldname2 and retrieve max(time). if you want the id too which can't be included in the group you'd better use a raw sql statement.

[PRACHI VAKHARIA]

Use the argument groupby instead of distinct, and you'll get the desired results.

so groupby fieldname2 and retrieve max(time).

What does groupby do exactly?
1– Does it group rows by a certain column's values – such that repeating values are hidden?
2– Does it group rows by a certain column's values – such that repeating values are grouped together?

How does it differ from distinct exactly, if answer is case 1?


Thank you, sesenmaister and Niphlod.
GroupBy works for my requirements in this case!
So, thanks a lot for reading and helping me out.


— PRACHI —