Complex SQL query in Android SQLite

Question

I have a table orders, consisting of 3 columns:

order_id int primary key,
cust_id integer,
order_date integer

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:

10 1325894400 (formatted as human readable date)
11 1325721600
12 1326326400

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?

asantaballa · Accepted Answer · 2013-10-14 17:20:30Z

1

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);

answered Oct 14, 2013 at 17:20

asantaballa

4,0581 gold badge24 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

asantaballa Over a year ago

Whew, great to hear! :-)

wyoskibum · Accepted Answer · 2013-10-14 17:24:44Z

1

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

answered Oct 14, 2013 at 17:24

wyoskibum

1,8592 gold badges23 silver badges43 bronze badges

2 Comments

wyoskibum Over a year ago

It's easy to forget about the SQL functions like max(), min().

Matt Minton Over a year ago

Or the fact that you have to pair GROUP BY with them.

AlexBcn · Accepted Answer · 2013-10-14 17:23:01Z

0

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.

answered Oct 14, 2013 at 17:23

AlexBcn

2,4602 gold badges19 silver badges29 bronze badges

Collectives™ on Stack Overflow

Complex SQL query in Android SQLite

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related