Group by - 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

Site Related Forums

Article Discussion

Group by

Author

Topic

minimotorsport
Starting Member

13 Posts

Posted - 2002-03-14 : 12:45:29

I have a table:
Group Title
------------ -----------------
MIN1000#00 BRAKE
MIN1000#10 pads
MIN1000#20 Bolts
MIN1000#30 Disks
MIN2000#00 AUTOMATIC
MIN2000#10 Gears
MIN2000#20 Flywheel
........

I need a query that gives my the following result

Group Title
----------------------------
MIN2000#00 AUTOMATIC
MIN2000#20 Flywheel
MIN2000#10 Gears
MIN1000#00 BRAKE
MIN1000#20 Bolts
MIN1000#30 Disks
MIN1000#10 pads

I hope this is clear. The Group ending with #00 are the main groups that should be ordered by ABC...
Groups with the same number before the # are the sub group that belong to the main group and should be ordered ABC.. under each main group.

yours faithfully

Michael

Edited by - minimotorsport on 03/14/2002 12:49:48

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-14 : 13:00:04

You can do a substring group by like group by left(groupvalue,7), title. Something like that.

This worked for me:

CREATE TABLE FOO2 (GROUPING VARCHAR(10), TITLE VARCHAR(10))

INSERT INTO FOO2 (GROUPING, TITLE) VALUES ('MIN1000#00','BRAKE')
INSERT INTO FOO2 (GROUPING, TITLE) VALUES ('MIN1000#10','pads')
INSERT INTO FOO2 (GROUPING, TITLE) VALUES ('MIN1000#20','Bolts')
INSERT INTO FOO2 (GROUPING, TITLE) VALUES ('MIN1000#30','Disks')
INSERT INTO FOO2 (GROUPING, TITLE) VALUES ('MIN2000#00','AUTOMATIC')
INSERT INTO FOO2 (GROUPING, TITLE) VALUES ('MIN2000#10','Gears')
INSERT INTO FOO2 (GROUPING, TITLE) VALUES ('MIN2000#20','Flywheel')

SELECT * FROM FOO2
ORDER BY LEFT(GROUPING,7) DESC,TITLE

Edited by - JamesT on 03/14/2002 13:06:41

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-14 : 13:58:21

JamesT,

You almost had it. BRAKE should be before Bolts because it is a Major Category #00.

Try the following:

SELECT aa.group_title
FROM (SELECT a.maj_cat, b.sub_cat, b.group_title, CASE
WHEN b.sub_cat = a.maj_cat THEN 1
ELSE 2
END SORT1
FROM (SELECT LEFT(group_title,7) group1,
SUBSTRING(group_title,12, LEN(group_title)-11) maj_cat
FROM GROUPS
WHERE SUBSTRING(group_title, 9,1) = '0') a,
(SELECT LEFT(group_title,7) group1,
SUBSTRING(group_title,12, LEN(group_title)-11) sub_cat,
group_title
FROM GROUPS) b
WHERE a.group1 = b.group1) aa
ORDER BY aa.maj_cat, aa.Sort1, aa.sub_cat

Let me know if it works. It should I tested the darn thing!

Jeremy

minimotorsport
Starting Member

13 Posts

Posted - 2002-03-15 : 06:14:44

Thanks, will try it today. And let you know.

yours faithfully

Michael

minimotorsport
Starting Member

13 Posts

Posted - 2002-03-15 : 06:43:59

@joldham

Just tryed it. But I have to change some names. I would like to know how it works. Can you please tell me ?

yours faithfully

Michael

minimotorsport
Starting Member

13 Posts

Posted - 2002-03-15 : 07:06:36

@joldham

Oh. You thought the Table has only one columb ? No. The Table Name is GRUPPEN. The first columb are the numbers MINxxxx#xx named "Gruppe" the 2th is the Grouptitle named "Bezeichnung".

yours faithfully

Michael

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-15 : 08:13:47

Michael,

I have modified the query with your table names.

SELECT aa.gruppe, aa.Bezeichnung
FROM (SELECT a.Bezeichnung Maj_Cat, b.Bezeichnung sub_cat, b.*, CASE
WHEN b.Bezeichnung = a.Bezeichnung THEN 1
ELSE 2
END SORT1
FROM (SELECT *
FROM GRUPPEN
WHERE SUBSTRING(Gruppe, 9,1) = '0') a,
GRUPPEN b
WHERE Left(a.Gruppe,7) = Left(b.Gruppe,7)) aa
ORDER BY aa.maj_cat, aa.Sort1, aa.sub_cat

What I did was get the major categories with this
SELECT *
FROM GRUPPEN
WHERE SUBSTRING(Gruppe, 9,1) = '0'
This will return Automatic and Brakes.

I then joined this query with the original table on the Left 7 characters of the Gruppe column (MIN2000) and also create a sort column to determine whether or not the line was a major category or a sub category for all the rows by determining if the Maj_cat matched the sub_cat at once with this
(SELECT a.Bezeichnung Maj_Cat, b.Bezeichnung sub_cat, b.gruppe, CASE
WHEN b.Bezeichnung (sub_cat) = a.Bezeichnung
(maj_cat) THEN 1
If they are equal, then the row is a major category and she be placed first in our sort list
ELSE 2
END SORT1
FROM (SELECT *
FROM GRUPPEN
WHERE SUBSTRING(Gruppe, 9,1) = '0') a,
GRUPPEN b
WHERE Left(a.Gruppe,7) = Left(b.Gruppe,7)) aa

Resulting in
Maj_cat Bezeichnung Gruppe SORT1
AUTOMATIC AUTOMATIC MIN2000#00 1
AUTOMATIC Flywheel MIN2000#20 2
AUTOMATIC Gears MIN2000#10 2
BRAKE Disks MIN1000#30 2
BRAKE Bolts MIN1000#20 2
BRAKE BRAKE MIN1000#00 1
BRAKE pads MIN1000#10 2

From this I Sort by Maj_Cat first, then Sort1, and finally Bezeichnung.

If you have any more questions, please let me know.

Jeremy

minimotorsport
Starting Member

13 Posts

Posted - 2002-03-15 : 10:49:28

Dear Jeremy,

thank you verrrrrrrry much.

This thread should be highlighted !!!

yours faithfully

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources