Ormlite orderBy max(ColumnName)

Question

Hi I need to use order by max(columnName) in ORMLite. I have the SQL query but I need to know how this query is used. This is my query:

SELECT  * FROM table where place = 'somePlace' group by name
    order by MAX (statusDate)

statusDate column contains date in "yyyy-dd-mm" format. The result I got is the list with recentDates.

To be sure to understand ? You need to know how to make your sql request with ormlite ? — HoodVinci, Sep 25 '13 at 13:34
@HoodVinci Yes exactly i need the corresponding ormlite query of the above — Sujiz, Sep 25 '13 at 13:45
What is the difference btw ordering by `statusDate` versus order by its MAX? I think you should just use `order by` and remove the `MAX` keyword. — Gray, Sep 25 '13 at 20:15
@Gray that is because i got same names with different dates and i need only the recent date . — Sujiz, Sep 26 '13 at 04:30

score 2 · Answer 1 · edited May 23 '17 at 11:52

2

Use a query builder, and function where and orderBy to preoceed

QueryBuilder<YourObject, Integer> q = yourDaoObject.queryBuilder();
Where<YourObject, Integer> wh = q.where();
wh.eq("place", "some_place");
q.orderBy("statusDate", false);
List<YourListOfObects> yourList = q.query();

But before that you should store a long instead to store your Date https://stackoverflow.com/a/6993420/2122876

edited May 23 '17 at 11:52

Community

1
1

answered Sep 25 '13 at 14:09

HoodVinci

656
5
7

Thanks for the the answer but i need the max(statusDate) it is for getting the latest name entry.so that the result list will contain only the datas with recent dates – Sujiz Sep 26 '13 at 04:34

Gray · Answer 2 · 2013-09-26T15:45:54.113

0

i got same names with different dates and i need only the recent date.

If you are trying to get element from Table with the maximum statusDate then you should be doing an descending order-by with a limit of 1. Something like:

QueryBuilder<Foo, Integer> qb = fooDao.queryBuilder();
qb.where().eq("place", "some_place");
qb.orderBy("sttusDate", false); // descending sort
// get the top one result
qb.limit(1);
Foo result = qb.queryForFirst();

edited Sep 26 '13 at 15:45

answered Sep 26 '13 at 13:25

Gray

115,027
24
293
354

but i need all the list from a single query.that is in some names there is only one date and for some there are different dates ,and i need to get all the list(ie if same names with different date choose its max date and if only one name choose its corrresponding date ) – Sujiz Sep 27 '13 at 05:32

score 0 · Answer 3 · answered Jan 06 '15 at 20:09

I did something like this. Please create your own query builder on the first line.

QueryBuilder<MyRowObject, Integer> queryBuiler = "Get Query builder" //getDaoXXX().queryBuilder();

MyRowObject firstLatestRow = queryBuiler.orderBy("dateColoumn", false).queryForFirst();

Hope this helps

Ormlite orderBy max(ColumnName)

3 Answers3