SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Group by
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

minimotorsport
Starting Member

Germany
13 Posts

Posted - 03/14/2002 :  12:45:29  Show Profile  Visit minimotorsport's Homepage  Reply with Quote
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

USA
97 Posts

Posted - 03/14/2002 :  13:00:04  Show Profile  Reply with Quote
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
Go to Top of Page

joldham
Wiseass Yak Posting Master

USA
300 Posts

Posted - 03/14/2002 :  13:58:21  Show Profile  Reply with Quote
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

Go to Top of Page

minimotorsport
Starting Member

Germany
13 Posts

Posted - 03/15/2002 :  06:14:44  Show Profile  Visit minimotorsport's Homepage  Reply with Quote
Thanks, will try it today. And let you know.


yours faithfully

Michael
Go to Top of Page

minimotorsport
Starting Member

Germany
13 Posts

Posted - 03/15/2002 :  06:43:59  Show Profile  Visit minimotorsport's Homepage  Reply with Quote
@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
Go to Top of Page

minimotorsport
Starting Member

Germany
13 Posts

Posted - 03/15/2002 :  07:06:36  Show Profile  Visit minimotorsport's Homepage  Reply with Quote
@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
Go to Top of Page

joldham
Wiseass Yak Posting Master

USA
300 Posts

Posted - 03/15/2002 :  08:13:47  Show Profile  Reply with Quote
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

Go to Top of Page

minimotorsport
Starting Member

Germany
13 Posts

Posted - 03/15/2002 :  10:49:28  Show Profile  Visit minimotorsport's Homepage  Reply with Quote
Dear Jeremy,

thank you verrrrrrrry much.

This thread should be highlighted !!!




yours faithfully

Michael
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000