ORDER BY Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ORDER BY Question

Author

Topic

rythm123us
Starting Member

27 Posts

Posted - 2005-09-11 : 22:43:16

Item Quantity
I1 10
I2 7
I1 5

Assume that is the Data.
I want it ordered by Quantity first and then show the same Item again
if it exists first.

How can I display it in this way
Item Quantity
I1 10
I1 5
I2 7

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-11 : 23:04:37

[code]
-- Item Quantity
-- I1 10
-- I2 7
-- I1 5
--
-- Assume that is the Data.
-- I want it ordered by Quantity first and then show the same Item again
-- if it exists first.
--
-- How can I display it in this way
-- Item Quantity
-- I1 10
-- I1 5
-- I2 7

DECLARE @quantity TABLE(item CHAR(2), quantity INT)

INSERT @quantity(item, quantity)
SELECT 'I1', 10 UNION ALL
SELECT 'I2', 7 UNION ALL
SELECT 'I1', 5

SELECT
item,
quantity
FROM
@quantity
ORDER BY
item,
quantity DESC
[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rythm123us
Starting Member

27 Posts

Posted - 2005-09-11 : 23:18:38

That would not work. As I said in my original Post, I want it ordered by Quantity first.

The query you specified orders by Item first and then Quanity.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-12 : 00:53:57

Did you try that query?
If you want the records to be displayed Descending order of quantities for each item, then the query suggested by derrick will work
Otherwise you need to give more sample data and expected result

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-12 : 01:31:04

[code]
DECLARE @quantity TABLE(item CHAR(2), quantity INT)

INSERT @quantity(item, quantity)
SELECT 'I1', 10 UNION ALL
SELECT 'I2', 7 UNION ALL
SELECT 'I1', 5

SELECT
Q.item,
quantity
FROM
@quantity Q
JOIN
(
SELECT M.item,
[MAX_QTY] = MAX(quantity)
FROM @quantity M
GROUP BY M.item
) M
ON M.item = Q.item
ORDER BY
M.MAX_QTY DESC,
Q.item,
quantity DESC
[/code]
Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-12 : 01:46:05

I think what you're saying you want and what you are showing us you want are two different things. Why don't you think about it and post a better example. Include a bigger sample of data, so we can decipher what you're trying to say.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2005-09-12 : 07:42:52

Seemed clear to me

until I discover I've answered the wrong question!, although the sample data did not express the edge-conditions at all

Kristen

rythm123us
Starting Member

27 Posts

Posted - 2005-09-12 : 10:05:19

The answer provided by Kristen works.
I am sorry for not providing proper Data.
Here is what the entire thing looks like.

How about this:
OrderID Item Quantity
1 I1 10
1 I2 7
1 I1 5
2 I3 15
2 I1 10
2 I3 4

The result I am hoping to generate:
OrderID Item Quantity
1 I1 10
1 I1 5
1 I2 7
2 I3 15
2 I3 4
2 I1 10

So the order should be OrderID, Quantity DESC, and the related Item.

an ORDER BY clause : "ORDER BY OrderID, Quantity DESC, Item"
would give me the result below. Which not what I want.
OrderID Item Quantity
1 I1 10
1 I2 7
1 I1 5
2 I3 15
2 I1 10
2 I3 4

DECLARE @quantity TABLE(OrderID INT, Item CHAR(2), Quantity INT)

INSERT INTO @quantity(OrderID, Item, Quantity)
VALUES(1, 'I1', 10)
INSERT @quantity(OrderID, Item, Quantity)
VALUES(1, 'I2', 7)
INSERT @quantity(OrderID, Item, Quantity)
VALUES(1, 'I1', 5)
INSERT @quantity(OrderID, Item, Quantity)
VALUES(2, 'I3', 15)
INSERT @quantity(OrderID, Item, Quantity)
VALUES(2, 'I1', 10)
INSERT @quantity(OrderID, Item, Quantity)
VALUES(2, 'I3', 4)

SELECT
OrderID,
Q.item,
quantity
FROM
@quantity Q
JOIN
(
SELECT M.item,
[MAX_QTY] = MAX(quantity)
FROM @quantity M
GROUP BY M.item
) M
ON M.item = Q.item
ORDER BY
OrderID, M.MAX_QTY DESC,
Q.item,
quantity DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources