|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
need help for a queryHere are the records : house kitchen room garage livingroom bathroom I want to do a select that gets all those records ordered alphabetically (so bathroom comes first normally) BUT house should come first, and then the other records sorted. house bathroom garage kitchen livingroom room I've tried : SELECT ID , Location FROM Estate WHERE Location like 'House' UNION SELECT ID ,Location FROM Estate WHERE Location not like 'House' order by Location But that sorts everything... and House doesnot come first.... Can you help ? Thanks In the future it's best to post some script that builds things to test.
It think this will give you what you want. CREATE TABLE #t1 ( ID INT IDENTITY PRIMARY KEY, Item VARCHAR(50) ) INSERT INTO #t1 VALUES ('house') INSERT INTO #t1 VALUES ('kitchen') INSERT INTO #t1 VALUES ('room') INSERT INTO #t1 VALUES ('garage') INSERT INTO #t1 VALUES ('livingroom') INSERT INTO #t1 VALUES ('bathroom') SELECT A.Item FROM (SELECT CASE Item WHEN 'house' THEN 1 ELSE 2 END AS ord, Item FROM #t1) AS A ORDER BY A.ord, A.Item Dan Show quote "samuelberthe***@googlemail.com" <samuelberthe***@googlemail.com> wrote in message news:1152893820.543700.108220@p79g2000cwp.googlegroups.com: > Hi, > Here are the records : > > house > kitchen > room > garage > livingroom > bathroom > > I want to do a select that gets all those records ordered > alphabetically (so bathroom comes first normally) BUT house should come > first, and then the other records sorted. > > house > bathroom > garage > kitchen > livingroom > room > > I've tried : > > > SELECT ID , Location FROM Estate > WHERE Location like 'House' > UNION > SELECT ID ,Location FROM Estate > WHERE Location not like 'House' > order by Location > > But that sorts everything... and House doesnot come first.... > Can you help ? > > Thanks samuelberthe***@googlemail.com wrote:
Show quote > Hi, Aside from the fact that you, as a human, can look at this list and know > Here are the records : > > house > kitchen > room > garage > livingroom > bathroom > > I want to do a select that gets all those records ordered > alphabetically (so bathroom comes first normally) BUT house should come > first, and then the other records sorted. > > house > bathroom > garage > kitchen > livingroom > room > > I've tried : > > > SELECT ID , Location FROM Estate > WHERE Location like 'House' > UNION > SELECT ID ,Location FROM Estate > WHERE Location not like 'House' > order by Location > > But that sorts everything... and House doesnot come first.... > Can you help ? > > Thanks > that "house" should come first, what attribute of the DATA indicates that "house" should come first? You should define some method of ranking, possibly a parent/child relationship (i.e. "kitchen" is a child of "house"). That would make it possible to handle this in a generic fashion, as opposed to hard-coding an order. If you can't do that, then this should get you what you want: SELECT CASE WHEN Location = 'House' THEN 1 ELSE 2 END AS SortKey, ID, Location FROM Estate ORDER BY SortKey, Location What happens when you add 'Apartment'?
Does it come before 'room', 'kitchen', etc., -BUT does it come after 'house' (Unless of course, the 'house' has an 'apartment' in it.) As Tracy indicated, you should have some form of parent-child relationship indicator in the data. Perhaps even Parent-Child tables. -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:%23$%231oL2pGHA.756@TK2MSFTNGP05.phx.gbl... > samuelberthe***@googlemail.com wrote: >> Hi, >> Here are the records : >> >> house >> kitchen >> room >> garage >> livingroom >> bathroom >> >> I want to do a select that gets all those records ordered >> alphabetically (so bathroom comes first normally) BUT house should come >> first, and then the other records sorted. >> >> house >> bathroom >> garage >> kitchen >> livingroom >> room >> >> I've tried : >> >> >> SELECT ID , Location FROM Estate >> WHERE Location like 'House' >> UNION >> SELECT ID ,Location FROM Estate >> WHERE Location not like 'House' >> order by Location >> >> But that sorts everything... and House doesnot come first.... >> Can you help ? >> >> Thanks >> > > Aside from the fact that you, as a human, can look at this list and know > that "house" should come first, what attribute of the DATA indicates that > "house" should come first? You should define some method of ranking, > possibly a parent/child relationship (i.e. "kitchen" is a child of > "house"). That would make it possible to handle this in a generic > fashion, as opposed to hard-coding an order. > > If you can't do that, then this should get you what you want: > > SELECT > CASE WHEN Location = 'House' THEN 1 ELSE 2 END AS SortKey, > ID, > Location > FROM Estate > ORDER BY SortKey, Location > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com  |
|||||||||||||||||||||||
Other interesting topics