November 3, 2011 at 7:45 pm
Dear Pros,
I need specific grouping, please see example...
Example Data:
[font="Courier New"]
PRICE - SOME_TEXT - ID
----- - --------- - --
11000 - AAAAAAAAA - 2
12000 - BBBBBBBBB - 3
13000 - CCCCCCCCC - 1
14000 - DDDDDDDDD - 1
15000 - EEEEEEEEE - 2
16000 - FFFFFFFFF - 3
17000 - GGGGGGGGG - 3
18000 - HHHHHHHHH - 1
19000 - IIIIIIIII - 2
[/font]
I want to show the highest price at the top row but also want to show the same group rows stay together (grouping made by ID column), like:
[font="Courier New"]
PRICE - SOME_TEXT - ID
----- - --------- - --
19000 - IIIIIIIII - 2
15000 - EEEEEEEEE - 2
11000 - AAAAAAAAA - 2
18000 - HHHHHHHHH - 1
14000 - DDDDDDDDD - 1
13000 - CCCCCCCCC - 1
17000 - GGGGGGGGG - 3
16000 - FFFFFFFFF - 3
12000 - BBBBBBBBB - 3
[/font]
Thanks for your helps...
November 3, 2011 at 7:50 pm
So, the Group with the max price at the top with individual prices in the same group sorted by in desc order. what about the rest of the groups/rows? they should follow the order of the ID ?
November 3, 2011 at 8:05 pm
How will this do?
DECLARE @Table TABLE
( PRICE INT,
SOME_TEXT VARCHAR(15),
ID INT
)
INSERT INTO @Table
VALUES
( 11000 , 'AAAAAAAAA', 2 )
,(12000 , 'BBBBBBBBB', 3 )
,(13000 , 'CCCCCCCCC', 1 )
,(14000 , 'DDDDDDDDD', 1 )
,(15000 , 'EEEEEEEEE', 2 )
,(16000 , 'FFFFFFFFF', 3 )
,(17000 , 'GGGGGGGGG', 3 )
,(18000 , 'HHHHHHHHH', 1 )
,(19000 , 'IIIIIIIII', 2 )
; WITH RowWithMaxValue AS
(
SELECT TOP 1 PRICE , SOME_TEXT , ID
FROM @Table
ORDER BY PRICE DESC
)
SELECT * FROM @Table
ORDER BY CASE WHEN ID = ( SELECT ID FROM RowWithMaxValue ) THEN 1
ELSE 2
END ,
ID ASC,
PRICE DESC
November 3, 2011 at 8:15 pm
Even better solution
DECLARE @MaxIDoFMaxPrice INT
; WITH RowWithMaxValue AS
(
SELECT TOP 1 ID
FROM @Table
ORDER BY PRICE DESC
)
SELECT @MaxIDoFMaxPrice = ID FROM RowWithMaxValue
SELECT * FROM @Table
ORDER BY CASE WHEN ID = @MaxIDoFMaxPrice THEN 1 ELSE 2 END ,
ID ASC,
PRICE DESC
November 3, 2011 at 8:47 pm
Here's some code that does that: it orders the groups by their max price (descending), and the rows with a group by their price (descending). Uses table name "thing" with columns PRICE,STEXT,ID.
If you want the rows with the non-max groups in ID order instead of in descending order of max price, you could use ColdCoffee's solution.
This could use a single CTE instead of two, but is probably easier to understand with two.
with mx (ID,MP) as (select ID, MAX(PRICE) MP from thing group by ID),
rnk (ID,RN) as (select ID, ROW_NUMBER() over(order by MP desc) as RN from mx)
select thing.PRICE, thing.STEXT, thing.ID
from thing inner join rnk on thing.ID = rnk.ID
order by rnk.RN asc, thing.price desc
Tom
November 3, 2011 at 8:56 pm
L' Eomot Inversé (11/3/2011)
Here's some code that does that: it orders the groups by their max price (descending), and the rows with a group by their price (descending).Could use a single CTE instead of two, but this is probably easier to understand.
with mx (ID,MP) as (select ID, MAX(PRICE) MP from thing group by ID),
rnk (ID,RN) as (select ID, ROW_NUMBER() over(order by MP desc) as RN from mx)
select thing.PRICE, thing.STEXT, thing.ID
from thing inner join rnk on thing.ID = rnk.ID
order by rnk.RN asc, thing.price desc
:pinch: 2 passes to the main table, 2 passed on the CTE. May bite the dust if the table is a huge one.
November 4, 2011 at 5:54 am
Please see drew's solution below...
November 4, 2011 at 1:23 pm
ColdCoffee (11/3/2011)
Even better solution
DECLARE @MaxIDoFMaxPrice INT; WITH RowWithMaxValue AS
(
SELECT TOP 1 ID
FROM @Table
ORDER BY PRICE DESC
)
SELECT @MaxIDoFMaxPrice = ID FROM RowWithMaxValue
SELECT * FROM @Table
ORDER BY CASE WHEN ID = @MaxIDoFMaxPrice THEN 1 ELSE 2 END ,
ID ASC,
PRICE DESC
I'm not sure that this solution is correct. I thought that the OP was asking for EACH group to be sorted by their max value. This is only sorting the group(s) with the overall max at the top and everything else by ID.
Here is the solution for the way I interpreted the specs:
SELECT *
FROM @Table
ORDER BY Max(Price) OVER( PARTITION BY ID ) DESC
, ID
, PRICE DESC
If you change the price for 'HHHHHHH' to 16000, you will see how the two orders are different.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 4, 2011 at 3:14 pm
Actually you'r right drew, I change the value of HH* to 16000 and the ColdCoffee's solution fails.
Many thanks for the more accurate and short way to the query...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply