Is it possible to do this in one select statement? Or how would I go about doing this? I'd have to do two ORDER BYs.
select id, Total
from (
select id, Total
from bar
order by id desc
limit 10
) baz
order by Total desc;
I think doing a simple select id, Total from bar order by id desc, Total desc limit 10; won't work since sorting by id is already exhaustive.
I have like 5 columns total, including id and Total. Would I select these on the first line, inside the from() or both?
Sorry, that confuses me a little bit. Oh, and what is baz on the 2nd to the last line?
The baz is a table alias for the inner select, which I believe mysql requires (although I haven't verified if you can omit it). It allows you to explicitly name the columns in the outer select, e.g.
select baz.id, foo.name
from foo, -- regular table
(
select id, frob from bar
) baz -- a sub-select
where foo.id = baz.frob -- something that joins the two
You can see there that it helps to disambiguate between foo.id and baz.id, that's why it's good that sub-selects can be named.
Sorry to bug you, you've been much help already. I am confused by this particular code.
select baz.id, foo.name
from foo, -- regular table
(
select id, frob from bar
) baz -- a sub-select
where foo.id = baz.frob -- something that joins the two
I know it's pseudo code but I was wondering if perhaps you could show me a small working sample with columns: id, url, images and Total? I am pretty confused by what you mean here. The table is "temp".Thank you.
select id, url, images, Total
from (
select *
from temp
order by id desc
limit 10
) last10
order by Total desc;
As for my pseudo-code you're confused about, that's only relevant if you have two tables that you might want to cross reference. As a more realistic example, suppose you have a database with Customers and Orders, and you want to find out your top ten buyers. You could do that like this:
select Customers.name, sales.amount -- select name and total amount spent.
from Customers,
( -- a sub-select to find the 10 best buyers.
select customer_id, sum(order_amount) as amount -- This selects a column and an aggregate
from Orders -- from the real table Orders.
group by customer_id
order by amount desc
limit 10
) sales -- we name this sub-select "sales".
where sales.customer_id = Customers.id -- we INNER JOIN the two "tables",
-- so we can cross-reference Orders against Customers.
As you can see, the "sales" sub-select isn't a real table in the database, but within this query we can treat it as if it were. We can select columns from it, and put extra constraints on it in a where clause. I hope it's a bit clearer now :)
SELECT id, total FROM table ORDER BY id, total DESC LIMIT 10;
That should do the trick. It orders by id (ascending which is the default), and then orders by total in descending order.
If you're using MySQL see their manual.
If you're using PostgreSQL see their manual.
And that's exactly why I offered the sub-select as a solution: first grab the 10 most recent id's, then sort those on Total. You have to do the ordering in two steps, no way around that.
SELECT id, total
FROM some_table
WHERE id > (SELECT MAX(id) - 10)
ORDER BY total DESC
my $data = qq[SELECT id, url, images, Total FROM temp WHERE id > (SELECT MAX(id) - 10) ORDER BY Total DESC];
SELECT id, total
FROM some_table
WHERE id > (SELECT MAX(id) FROM some_table) - 10
ORDER BY total DESC