Order a group by value

Question

Order a group by value

FastRider30

SSC Veteran

Points: 293
More actions
December 29, 2009 at 12:14 pm

#96365

Hi all,
I need to display a grid of parts, order by quantity but with a twist... parts are Lefts and Rights so if I order the quantity of a Left I need to show the correspondent Right right next to it, regardless of the quantity of that part.
Example:
A - Left Side
B - Right Side
C - Left Side
D - Right Side
PART | TYPE | QTY
A | XYZ | 16
B | XYZ | 11
C | TTY | 10
D | TTY | 20
The result should show:
PART | TYPE | QTY
C | TTY | 10
D | TTY | 20
B | XYZ | 11
A | XYZ | 16
Its not important that the Right come always first or vice-versa.
Thank you all.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Rider i added a CASE in order by with two columns, and it seems to do exactly what you were after; am i missing anything?:

here's my results:

PART TYPE QTY

C TTY 10

D TTY 20

A XYZ 16

B XYZ 11

here's my code:

--create a temp table with a bunch o union all statements

SELECT 'A' AS PART,'XYZ' AS TYPE,16 AS QTY INTO #TMP UNION ALL

SELECT 'B' AS PART,'XYZ' AS TYPE,11 AS QTY UNION ALL

SELECT 'C' AS PART,'TTY' AS TYPE,10 AS QTY UNION ALL

SELECT 'D' AS PART,'TTY' AS TYPE,20

SELECT * FROM #TMP --not the order we wanted

SELECT * FROM #TMP ORDER BY TYPE,PART--almost

SELECT * FROM #TMP ORDER BY TYPE,CASE WHEN PART IN('A','C') THEN 1 ELSE 2 END --the correct order

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

FastRider30 SSC Veteran Points: 293 More actions · Answer 2

Hi,

Thanks for your reply.

If you do it that way the order for part TTY is correct but not for XYZ.

B should be before A.

The lowest number should always be first.

Mike01 SSChampion Points: 11395 More actions · Answer 3

You can changes Lowell's code to order by quantity as well