CODEWITHSUNDEEP
androidsqlsqlite
Matt Minton
Matt Minton

Reputation: 13

Complex SQL query in Android SQLite

I have a table orders, consisting of 3 columns:

with data:

order_id  |  cust_id  |  order_date
1         |  10       |  1325376000
2         |  10       |  1325548800
3         |  10       |  1325894400
4         |  11       |  1325462400
5         |  11       |  1325721600
6         |  12       |  1325721600
7         |  12       |  1326326400

I'm trying to write a query to give a Cursor containing the most recent order for a given customer that I can then pass to a SimpleCursorAdapter and bind to a ListView, such that the user sees the following:

I've tried joining the table to itself in various ways without any luck:

http://sqlfiddle.com/#!2/77b22d/1/0

If I have to populate an ArrayList and use an ArrayAdapter I will, but I'd like to exhaust this option first. Thanks!

EDIT: Apologize for the differences between here and the SQLFiddle, brain running on two separate threads. The Fiddle is the 'correct' data set.

2nd EDIT: Added a new wrinkle (ignore table above, see the SQL fiddle). Adding a field for free-form text and then running the query returns the first record in the GROUP BY, plus the field for the max_date. I need to pull the whole record containing the date that equals max_date. Adding a WHERE clause breaks the query. Thoughts?

Upvotes: 1

Views: 1114

Answers (3)

wyoskibum
wyoskibum

Reputation: 1859

If you just want the latest record for each customer, I think this will work:

SELECT order_number, cust_number, max(order_date) as max_date FROM orders GROUP BY cust_number

Upvotes: 1

AlexBcn
AlexBcn

Reputation: 2460

The values you put on the link are different from the ones you posted here but you are looking for:

select o1.cust_number, max(o1.order_date)
from orders o1
group by o1.cust_number
order by o1.cust_number ASC

This will give you for each customer the most recent order.

Upvotes: 0

asantaballa
asantaballa

Reputation: 4048

Try this

select 
  order_number
, cust_number
, order_date
from orders o1
where order_number =
(
    select order_number
    from  orders o2
    where o2.cust_number = o1.cust_number
      and order_date =
        (  
            select max(order_date)
            from orders o3
            where o3.cust_number = o2.cust_number
         )
)

This will get you the correct records and you can format the date as you like in the main query.

Note: My answer is a bit different form your display since the example here and the Fiddle are different. used the Fiddle one

create table orders (order_number integer primary key,
                     cust_number integer not null,
                     order_date integer not null);

insert into orders values (1001,10,1005),
  (1,10,1325376000),
  (2,10,1325548800),
  (3,11,1325894400),
  (4,11,1325462400),
  (5,11,1325721600),
  (6,12,1325721600),
  (7,12,1326326400),
  (8,12,1326326460);

Upvotes: 1

Related Questions