Showing first 10 results for each day

[hessodreamy]

I feel I should be able to work this out, but I'm drawing a blank.

How would I retrieve, for example, the top 10 orders for each day for a period, where I order by orderTotal?

I can't seem to find any way to limit the results for each day, rather than the results as a whole.

Any ideas?

 

[audiopro]

Not sure of the syntax but try group by day, order by total

Keith

http://www.studiosoft.co.uk

 

[hessodreamy]

doesn't work. The group by day just limits the results to 1 entry per day, and shows details of the first order from the day. I can get totals for the day, but what I really want is the top 10 orders.

 

[guelphdad]

can you show some details of your table? do you have a date column? are your orders already totaled or are there individual line totals to consider.

 

[r937]

How would I retrieve, for example, the top 10 orders for each day for a period, where I order by orderTotal?

select t1.orderDate 
     , t1.orderID
     , t1.orderTotal
  from orders as t1
inner
  join orders as t2
    on t1.orderDate = t2.orderDate
   and t1.orderTotal <= t2.orderTotal
 where month(t1.orderTotal) = 2006   
   and month(t1.orderTotal) in (4,5,6)
group
    by t1.orderDate 
     , t1.orderID
     , t1.orderTotal
having count(*) <= 10
order
    by t1.orderDate 
     , t1.orderTotal desc

r937.com | rudy.ca

 

[hessodreamy]

That seemed to just kill mysql. Do you think you could explain how it works, so that I can optimise it? I can't really see what's happending.

 

[r937]

wow, you left it running for 10 days?

do you have any indexes defined on that table?

r937.com | rudy.ca

 

[hessodreamy]

I have indexes on orderID, and a unix timestamp field (I don't have a 'date' field).

So I used t1.uts>unix_timestamp('2006-02-01') in the where statement.
I'm not sure why you'd include the orderTotal in the join condition. Maybe I've explained the problem/definition badly?

 

[r937]

why do i include the order total in the join condition? because it's a theta join which joins every row to all the other rows with a higher order total for the same order date

this allows counting how many other rows have a greater order total, and thus in the HAVING clause, we can pick only original rows that have 10 or fewer rows with a greater total

thus, returning only the top 10 order totals for each order date

r937.com | rudy.ca

 

[hessodreamy]

ahhhhhh.... i think.

 

[hessodreamy]

Yes I think I've got my head around it now.
I think the main reason it was dying was that i was doing from_unixtime for the timestamp on every row. I added a `date` field on which to join and it worked fine.
From what i've read it seems that, in 4.1, there is not a way to set a `date` field which defaults to the current date (only datetime, which would, again, create work for the query to join on date). Or am I wrong on that?

 

[r937]

no, you are not wrong on that

however, if you want to default a column to the current timestamp, you should consider using a TIMESTAMP column instead of DATETIME

the first TIMESTAMP column in a table will be automatically set upon insert, and automatically updated every time the row is updated

r937.com | rudy.ca

 

[hessodreamy]

yes, but the problem I was finding was in joining the orders table to itself on the date, making a lot of work becuase it had to extract a date from the timestamp (twice).

It seems that the problem isn't solved if I'm using timestamp and still have to convert in order to create the join. Or maybe using from_unixtime is particularly inefficient?

 

[r937]

from_unixtime is a function, and any time you use a function on a column, you can't use an index search on that column

but your comment on the need to join on the date (rather than the datetime) is correct

therefore, i would declare a DATE column, and populate it on insert with CURRENT_DATE

unix timestamps are neat, but if you want to search on them, make sure you always put the "naked" column on one side of the search condition --

... where column = some expression

r937.com | rudy.ca