Stuck on T-sql with ordering of result set

Question

Stuck on T-sql with ordering of result set

Todd Bernstein-449184

SSC Enthusiast

Points: 199
More actions
January 22, 2010 at 1:12 pm

#144548

I have one table
Date PartGroup subcode QNTY
1-10-2010 ABC ABC.DB12 400
1-10-2010 ABC ABC.DB10 343
1-10-2010 ABC ABC.DB11 234
1-10-2010 ABC ABC.DB13 1
1-10-2010 BBB BBB.DB12 533
1-10-2010 BBB BBB.DB11 200
1-10-2010 BBB BBB.DB10 100
1-10-2010 YYY YYY.DB12 450
1-10-2010 ZZZ ZZZ.DB11 200
1-10-2010 ZZZ ZZZ.DB10 100
What I need is the results set to list PartGroup by Partgroup by largest QNTY
Result should be:
Date PartGroup subcode QNTY
1-10-2010 BBB BBB.DB12 533
1-10-2010 BBB BBB.DB11 200
1-10-2010 BBB BBB.DB10 100
1-10-2010 YYY YYY.DB12 450
1-10-2010 ABC ABC.DB12 400
1-10-2010 ABC ABC.DB10 343
1-10-2010 ABC ABC.DB11 234
1-10-2010 ABC ABC.DB13 1
1-10-2010 ZZZ ZZZ.DB11 200
1-10-2010 ZZZ ZZZ.DB10 100
Seems like it should be an easy query but I am stuck.
Any ideas Appreciated.
Thanks
Todd

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Todd Bernstein-449184 (1/22/2010)
I have one table
Date PartGroup subcode QNTY
1-10-2010 ABC ABC.DB12 400
1-10-2010 ABC ABC.DB10 343
1-10-2010 ABC ABC.DB11 234
1-10-2010 ABC ABC.DB13 1
1-10-2010 BBB BBB.DB12 533
1-10-2010 BBB BBB.DB11 200
1-10-2010 BBB BBB.DB10 100
1-10-2010 YYY YYY.DB12 450
1-10-2010 ZZZ ZZZ.DB11 200
1-10-2010 ZZZ ZZZ.DB10 100
What I need is the results set to list PartGroup by Partgroup by largest QNTY
Result should be:
Date PartGroup subcode QNTY
1-10-2010 BBB BBB.DB12 533
1-10-2010 BBB BBB.DB11 200
1-10-2010 BBB BBB.DB10 100
1-10-2010 YYY YYY.DB12 450
1-10-2010 ABC ABC.DB12 400
1-10-2010 ABC ABC.DB10 343
1-10-2010 ABC ABC.DB11 234
1-10-2010 ABC ABC.DB13 1
1-10-2010 ZZZ ZZZ.DB11 200
1-10-2010 ZZZ ZZZ.DB10 100
Seems like it should be an easy query but I am stuck.
Any ideas Appreciated.
Thanks
Todd

SELECT ...

FROM ...

ORDER BY PartGroup, QNTY DESC

Edit: Not quite but a start. I can't see how you get the order you do on PartGroup.

DDL for the table (CREATE TABLE statement), INSERT INTO statements to load the table would be very helpful.

Todd Bernstein-449184 SSC Enthusiast Points: 199 More actions · Answer 2

Thanks for your reply but that query gives me the partgroup in alpha order. I need the results by group byt partgroup with by qnty.

results should be:

Date PartGroup subcode QNTY

1-10-2010 BBB BBB.DB12 533

1-10-2010 BBB BBB.DB11 200

1-10-2010 BBB BBB.DB10 100

1-10-2010 YYY YYY.DB12 450

1-10-2010 ABC ABC.DB12 400

1-10-2010 ABC ABC.DB10 343

1-10-2010 ABC ABC.DB11 234

1-10-2010 ABC ABC.DB13 1

1-10-2010 ZZZ ZZZ.DB11 200

1-10-2010 ZZZ ZZZ.DB10 100

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I think I see what needs to be done, but without the DDL (CREATE TABLE statment) and sample data (as a series of INSERT INTO statements) I really can't do much more at the moment.

Todd Bernstein-449184 SSC Enthusiast Points: 199 More actions · Answer 4

CREATE TABLE [dbo].[TestTable](

[OrderDate] [datetime] NOT NULL,

[PartGroup] [varchar](6) NOT NULL,

[SubCode] [varchar](25) NOT NULL,

[QNTY] [int] NOT NULL

) ON [PRIMARY]

INSERT INTO [dbo].[TestTable] Values ('1-10-2010', 'ABC', 'ABC.DB12', 400)

INSERT INTO [dbo].[TestTable] Values ('1-10-2010', 'ABC', 'ABC.DB10', 343)

INSERT INTO [dbo].[TestTable] Values ('1-10-2010', 'ABC', 'ABC.DB11', 234)

INSERT INTO [dbo].[TestTable] Values ('1-10-2010', 'ABC', 'ABC.DB10', 1)

INSERT INTO [dbo].[TestTable] Values ('1-10-2010', 'BBB', 'BBB.DB12', 533)

INSERT INTO [dbo].[TestTable] Values ('1-10-2010', 'BBB', 'BBB.DB11', 200)

INSERT INTO [dbo].[TestTable] Values ('1-10-2010', 'BBB', 'BBB.DB10', 100)

INSERT INTO [dbo].[TestTable] Values ('1-10-2010', 'YYY', 'YYY.DB12', 450)

INSERT INTO [dbo].[TestTable] Values ('1-10-2010', 'ZZZ', 'ZZZ.DB11', 200)

INSERT INTO [dbo].[TestTable] Values ('1-10-2010', 'ZZZ', 'ZZZ.DB10', 100)

Mark Cowne One Orange Chip Points: 26984 More actions · Answer 5

Try this

SELECT ...

FROM ...

ORDER BY MAX(QNTY) OVER(PARTITION BY PartGroup) DESC, QNTY DESC

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Lynn Pettis

SSC Guru

Points: 442467

January 22, 2010 at 1:45 pm

#1107566

Perfect, Mark!

Mark Cowne One Orange Chip Points: 26984 More actions · Answer 7

Lynn Pettis (1/22/2010)
Perfect, Mark!

Thanks for the feedback!

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Mark-101232 (1/22/2010)
Lynn Pettis (1/22/2010)
Perfect, Mark!
Thanks for the feedback!

Wasn't the solution I was looking at, but it sure looks good. I was going to create a CTE and join to it.

Todd Bernstein-449184 SSC Enthusiast Points: 199 More actions · Answer 9

Almost... That query returns:

OrderDatePartGroupSubCodeQNTY

2010-01-10 00:00:00.000ZZZZZZ.DB11200

2010-01-10 00:00:00.000ZZZZZZ.DB10100

2010-01-10 00:00:00.000ABCABC.DB12400

2010-01-10 00:00:00.000ABCABC.DB10343

2010-01-10 00:00:00.000ABCABC.DB11234

2010-01-10 00:00:00.000ABCABC.DB101

2010-01-10 00:00:00.000YYYYYY.DB12450

2010-01-10 00:00:00.000BBBBBB.DB12 533

2010-01-10 00:00:00.000BBBBBB.DB11 200

2010-01-10 00:00:00.000BBBBBB.DB10 100

I need the result in this order.

Date PartGroup subcode QNTY

1-10-2010 BBB BBB.DB12 533

1-10-2010 BBB BBB.DB11 200

1-10-2010 BBB BBB.DB10 100

1-10-2010 YYY YYY.DB12 450

1-10-2010 ABC ABC.DB12 400

1-10-2010 ABC ABC.DB10 343

1-10-2010 ABC ABC.DB11 234

1-10-2010 ABC ABC.DB13 1

1-10-2010 ZZZ ZZZ.DB11 200

1-10-2010 ZZZ ZZZ.DB10 100

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Here is the result set I get when I run Mark's query:

OrderDate PartGroup SubCode QNTY

----------------------- --------- ------------------------- -----------

2010-01-10 00:00:00.000 BBB BBB.DB12 533

2010-01-10 00:00:00.000 BBB BBB.DB11 200

2010-01-10 00:00:00.000 BBB BBB.DB10 100

2010-01-10 00:00:00.000 YYY YYY.DB12 450

2010-01-10 00:00:00.000 ABC ABC.DB12 400

2010-01-10 00:00:00.000 ABC ABC.DB10 343

2010-01-10 00:00:00.000 ABC ABC.DB11 234

2010-01-10 00:00:00.000 ABC ABC.DB10 1

2010-01-10 00:00:00.000 ZZZ ZZZ.DB11 200

2010-01-10 00:00:00.000 ZZZ ZZZ.DB10 100

EDIT:

And here is my code:

SELECT *

FROM [dbo].[TestTable]

ORDER BY MAX(QNTY) OVER(PARTITION BY PartGroup) DESC, QNTY DESC ;

Todd Bernstein-449184 SSC Enthusiast Points: 199 More actions · Answer 11

Thanks both of you for your help. You led me in the right direction.

This query returns the result order that I needed:

SELECT * ,max(QNTY) OVER(PARTITION BY PartGroup ) as ordered

from [TestTable]

ORDER BY ordered desc ,qnty desc

Todd Bernstein-449184 SSC Enthusiast Points: 199 More actions · Answer 12

You are correct. Mark's orginal query worked perfect. I must of copied it wrong.

Again Thank you for your help.

Todd