I need to do what to me is an advanced sort. I have this two tables:
Table: Fruit 开发者_运维技巧 fruitid | received | basketid 1 20100310 2 2 20091205 3 3 20100220 1 4 20091129 2 Table: Basket id | name 1 Big Discounts 2 Premium Fruit 3 Standard Produce
I'm not even sure I can plainly state how I want to sort (which is probably a big part of the reason I can't seem to write code to do it, lol).
I do a join query and need to sort so everything is organized by basketid. The basketid that has the oldest fruit.received date comes first, then the other rows with the same basketid by date asc, then the basketid with the next earliest fruit.received date followed by the other rows with the same basketid, and so on.
So the output would look like this:
Fruitid | Received | Basket 4 20091129 Premuim Fruit 1 20100310 Premuim Fruit 2 20091205 Standard Produce 3 20100220 Big Discounts
Any ideas how to accomplish this in a single execution?
try this (sql server table setup code, but query should work in any database)
DECLARE @Fruit table (fruitid int, received int, basketid int)
INSERT @Fruit VALUES(1, 20100310, 2 )
INSERT @Fruit VALUES(2, 20091205, 3 )
INSERT @Fruit VALUES(3, 20100220, 1 )
INSERT @Fruit VALUES(4, 20091129, 2 )
DECLARE @Basket table (id int,basket varchar(20))
INSERT @Basket VALUES (1, 'Big Discounts' )
INSERT @Basket VALUES (2, 'Premium Fruit' )
INSERT @Basket VALUES (3, 'Standard Produce')
SELECT
f.Fruitid ,f.received,b.basket
FROM @Fruit f
INNER JOIN (SELECT
basketid, MIN(received) AS received
FROM @Fruit
GROUP BY basketid
) o ON f.basketid = o.basketid
INNER JOIN @Basket b ON o.basketid=b.id
ORDER BY o.received
OUTPUT
Fruitid received basket
----------- ----------- --------------------
4 20091129 Premium Fruit
1 20100310 Premium Fruit
2 20091205 Standard Produce
3 20100220 Big Discounts
(4 row(s) affected)
SELECT f.fruitid, f.received, ba.name AS basket
FROM Fruit f
JOIN (
SELECT basketid, MIN(received) AS mr
FROM fruit
GROUP BY
basketid
) b
ON f.basketid = b.basketid
JOIN basket ba
ON ba.id = f.basketid
ORDER BY
b.mr, f.basketid, f.received
精彩评论