sort order query

[anorthcote]

Hi,

I have the following data in a table

ID  CatID SubCatID      Description
7       7        0      Sprinklers
8       8        0      Intruder Alarm
9       9        0      Air Conditioning
22      9        1      Fans
23      9        2      Pumps

I'm writing a stored procedure to return these items grouped by category and sub category. In my example above 'Fans' and 'Pumps' are sub categories of 'Air Conditioning'. My stored procedure adds two spaces to the beginning of the sub category descriptions to help the user identify them as sub categories.

My question is - how can I sort the table into alphabetical order first by description where SubCatID is 0 then by description where SubCatID is not zero but still keeping rows where CatID is the same together.

Example

ID  CatID SubCatID      Description
9       9        0      Air Conditioning
22      9        1        Fans
23      9        2        Pumps
8       8        0      Intruder Alarm
7       7        0      Sprinklers

Does this make sense and can anyone help?

 

[cmartin75]

You can use the CASE statement in the ORDER clause
Like this:

ORDER BY CatId, CASE SubCatId WHEN 0 THEN '0' + Description ELSE '1' + Description END

If it doesn't delay too much your query, it would be a solution.

 

[anorthcote]

Thank you but that didn't do what I needed, I have worked out a solution involving creating a new column 'FullDescription' which is either 'category description' if SubCatID = 0 or 'Category description:Sub Category description' if SubCatID >0.

create table #temp
	([ID] int,
	 CatID int,
	 SubCatID int,
	 [Description] varchar(50),
	 FullDescription varchar(100),
	 IsActive int)


insert into #Temp
	([ID], CatID, SubCatID, [Description], IsActive)

	Select [ID], CatID, SubCatID, [Description], IsActive from Category

update #Temp
	Set FullDescription = b.[Description] 
		from Category b
			inner join #Temp a
			on a.catid = b.catid
			where b.subcatid = 0 

update #Temp
	Set FullDescription = FullDescription + ':' + [Description]
	where subcatid > 0


update #Temp 
	Set [Description] = '  ' + [Description] where SubCatID > 0



	Select [ID], [Description], IsActive, FullDescription from #Temp where catid in (7,8,9)
	order by FullDescription


drop table #temp

which returned

ID      Description     IsActive    FullDescription
9       Air Conditioning       1    Air Conditioning
22        Fans                 1    Air Conditioning:Fans
23        Pumps                1    Air Conditioning:Pumps
8       Intruder Alarm         1    Intruder Alarm
7       Sprinklers             1    Sprinklers

It works but isn't very tidy, can you think of any way of tidying it up at all?

Thanks

 

[tran008]

SQL2005:

declare @tbl1 table(ID int, CatID int, SubCatID int, Description varchar(35))
insert into @tbl1(id, catid,subcatid, description)
select 7, 7 , 0, 'Sprinklers' union all
select 8, 8 , 0 , 'Intruder Alarm' union all
select 9 , 9 , 0, 'Air Conditioning' union all
select 22, 9 , 1 , 'Fans' union all
select 23 , 9 , 2 , 'Pump'


select * from (select *, row_number() over ( order by catid desc, subcatid asc)as rowid
from @tbl1)b
order by rowid

 

[anorthcote]

I'm using SQL 2000

row_number() isn't recognised, do you have an alternative?

 

[RyanEK]

Hi,

You can use a derived table to hold the common parent description and id. Let me know if this works for you...

select id, catid, subcatid, description
from (
	select *,
	(select t2.description from @tbl1 t2 where t2.id = t1.catid) as tempdesc,
	(case when id = catid then id else catid end) as tempid
	from @tbl1 t1
) as t3
order by t3.tempdesc, t3.tempid

Ryan

 

[anorthcote]

Looks good but I have one issue with it - the sub categories are still not in alphabetical order. Using the above example where SubCatID 1 is Fans and SubCatID 2 is Pumps all looks good but if they were the other way around (which they may well be) then they are out of order ie

ID     CatID    SubCatID    Description
9          9           0    Air Conditioning
22         9           1    Pumps
23         9           2    Fans

It still looks like concatenating the CatID and SubCatID descriptions together is the best way but I'd like to tidy the code up.

Any further suggestions???

Thanks a lot for all your help.

 

[gmmastros]

It looks worse than it really is.

Select Id, 
       CatId, 
       SubCatId, 
       Case When SubCatDescription Is Not NULL Then '  ' Else '' End 
         + Coalesce(SubCatDescription, CatDescription) As Description
From   (
       Select A.Id, A.CatID, A.SubCatID, A.Description As CatDescription, Null As SubCatDescription
       From   Category As A
       Where  SubCatId = 0
 
       Union All

       Select B.Id, B.CatId, B.SubCatId, A.Description, B.Description
       From   Category As A
              Inner Join @tbl1 As B
                On A.CatId = B.CatId
                And A.SubCatId = 0
                And B.SubCatId > 0
       ) As A
Order By CatDescription, SubCatDescription

If you have any questions, let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anorthcote]

George,

I've had a look at the code and see your principal, my final code is

declare @tbl1 table(ID int, CatID int,  SubCatID int,     Description varchar(35))
insert into @tbl1(id, catid,subcatid, description)
select 7,       7 ,       0,      'Sprinklers' union all
select 8,       8 ,       0  ,    'Intruder Alarm' union all
select 9 ,      9 ,       0,      'Air Conditioning' union all
select 22,      9 ,       1 ,     'Fans' union all
select 23 ,     9  ,      2 ,     'Pump'



Select Id, 
       CatId, 
       SubCatId, 
       Case When SubCatDescription Is Not NULL Then '  ' Else '' End 
         + Coalesce(SubCatDescription, CatDescription) As Description
From   (
       Select A.Id, A.CatID, A.SubCatID, A.Description As CatDescription, Null As SubCatDescription
       From   Category As A
       Where  A.SubCatId = 0 
 
       Union All

       Select B.Id, B.CatId, B.SubCatId, A.Description, B.Description
       From   Category As A
              Inner Join @tbl1 As B
                On A.CatId = B.CatId
                And A.SubCatId = 0
                And B.SubCatId > 0
       ) As A
Order By CatDescription, SubCatDescription

I'm getting the error message

Server: Msg 457, Level 16, State 1, Line 11
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

 

[gmmastros]

That's my fault. Sorry.

I noticed it one of your earlier postings that you are using a table named Category. I replaced the table variable with that table name, but missed one.

declare @tbl1 table(ID int, CatID int,  SubCatID int,     Description varchar(35))
insert into @tbl1(id, catid,subcatid, description)
select 7,       7 ,       0,      'Sprinklers' union all
select 8,       8 ,       0  ,    'Intruder Alarm' union all
select 9 ,      9 ,       0,      'Air Conditioning' union all
select 22,      9 ,       1 ,     'Fans' union all
select 23 ,     9  ,      2 ,     'Pump'



Select Id,
       CatId,
       SubCatId,
       Case When SubCatDescription Is Not NULL Then '  ' Else '' End
         + Coalesce(SubCatDescription, CatDescription) As Description
From   (
       Select A.Id, A.CatID, A.SubCatID, A.Description As CatDescription, Null As SubCatDescription
       From   Category As A
       Where  A.SubCatId = 0
 
       Union All

       Select B.Id, B.CatId, B.SubCatId, A.Description, B.Description
       From   Category As A
              Inner Join [!]Category[/!] As B
                On A.CatId = B.CatId
                And A.SubCatId = 0
                And B.SubCatId > 0
       ) As A
Order By CatDescription, SubCatDescription

Your error message makes me think you may have other problems later. This is especially true is you start using temp tables and table variables alot.

Your error message indicates a collation conflict. This is nasty. Collations identify how strings are compared. Usually string comparisons are not case sensitive ( a = A ). This happens because of the collation. Collations also identify the sort order for characters. Which comes first, comma or period?

Now, here's where it gets ugly...

Every database has a default collation. When you create a string object (think column with data type of char, nchar, varchar, or nvarchar), if you do not specify the collation, it will be assigned the default collation for the database.

Like I said earlier, each database has a default collation. What's worse is that each database on your server could have a different collation. Now for the ugliness... when you use a temp table or table variable, it is created in the TempDB. Since you are getting this error, I would assume that the TempDB has a different collation than your database. This means that you will have a much more difficult time programming because you will constantly need to worry about collation problems. It is 1000 times easier to write SQL code if the collations match.

Just for fun....

Open a query window and issue this command.

sp_helpdb

You should get a row for each database on your server. You should notice a status column. This column contains a lot of data, one of which is the collation. You may need to widen this column to see all the data. Take a look at the row corresponding to your database and also the TempDB, and while you're at it, Model.

I suspect that the TempDB has a different collation than your database. Also... your Model database may be different. When you create a new database on your server, it takes on the characteristics of Model. In an ideal world, all of the collations would match.

Let me know what you find.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anorthcote]

Hi,

That code works very well, thank you.

My collation is set to

Collation=SQL_Latin1_General_CP1_CI_AS

on all databases but the one I'm working on which is

Collation=Latin1_General_CI_AS

Interestingly the SQLSortOrder is set to 52 on all but mine, which is 0

The difference in the databases is that the one I'm working on was written originally by an outside contractor and I'm now developing it further.

What is the difference between the two and which is better?

Thanks for all your help.

 

[gmmastros]

I'm not saying one is better than the other. What I'm saying is... it stinks when they don't match because it's much more difficult to write code.

You can change the collation, but it's a bit scary to do so (in my opinion). What's worse.... you could break your existing application if it depends on a certain collation.

If you don't want to change the collation, then you should get in to the habit of specifying the collation whenever you create a temp table or table variable. Using the previous example....

declare @tbl1 
table   (ID int, 
        CatID int,  
        SubCatID int,     
        Description varchar(35) [!]Collate Database_Default[/!])

insert into @tbl1(id, catid,subcatid, description)
select 7,       7 ,       0,      'Sprinklers' union all
select 8,       8 ,       0  ,    'Intruder Alarm' union all
select 9 ,      9 ,       0,      'Air Conditioning' union all
select 22,      9 ,       1 ,     'Fans' union all
select 23 ,     9  ,      2 ,     'Pump'

By using Database_Default as your collation, the column's collation will be the default collation of your database. Then, when you join, compare, or sort the data, it will happen the same in the table variable as in your database.

I recognize that this may all sound very confusing, and I apologize for that.

Let's leave it at....

Because your collations do not match, you must get in to the habit of specifying the collation whenever you use a temp table or table variable. This must be done for any type of string column (not numbers or bit, etc...) Like this...

Declare @Temp 
Table   (VarCharColumnName VarChar(20) [!]Collate Database_Default[/!], 
        TextColumnName Text [!]Collate Database_Default[/!],
        IntColumnName Int)

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[anorthcote]

What documentation is there on database default collation. We are just about to install SQL 2005 and go through the process of upgrading and migrating a big chunk of our databases on to the new server.

This issue does scare me slightly and I don't want anything to break.

I read somewhere that SQL_Latin1_General_CP1_CI_AS is the default if the server locale is English(US) and Collation=Latin1_General_CI_AS is English(UK), is that the case and can it be changed once SQL is installed?

Sorry to bombard you with questions and for drifting off-topic but you do seem to know your stuff.


Cheers.

 

[gmmastros]

you do seem to know your stuff.

Shh..... Don't tell anyone, but I'm really just faking it.

As far as I know, there aren't any changes on the way collations are handled between SQL2000 and SQL2005.

The only reason I know this stuff is because I got bit by this problem in my own app. I'm primarily a U.S. company, but I sold my software to an organization in Canada about 2 years ago and ran in to the same problem. There are a couple ways to handle it.

1. You could check your existing SQL code and make sure you are specifying the collation whenever you use a table variable or temp table.

2. You could create another copy of the database, change the collation to match the other databases (TempDB in particular) and then test the application thoroughly to make sure it performs properly. If you do this, you may want to contact the original author of the DB to see if changing the collation will have any adverse affects.

3. You could install a named instance of SQL Server. That named instance should have the same default collation as your database. So... effectively you would have 2 instances of SQL. One with the default that has your other databases and one with the collation of your database.

I can't really tell you what the best option is. Sorry. This is a decision for you to make.

I can tell you that we ended up installing a named instance because it was the fastest way to resolve the problem. We then went in and made sure we specify the collation on all temp tables and table variables. This took longer to do, but by installing a named instance, it got the immediate problem resolved and gave us the much needed time to convert the code.

My application is commercially available, so my database is installed on hundreds of different servers throughout the US and Canada. It's important for my application to be flexible, so we went with option 3, then option 1. If your database is only going to be on one server, and you have the time to completely test the application, then I would recommend option 2. Understand, though... it can be very time consuming (depending on your app), and business requirements (the time it takes to do this) may prevent it from happening.

While we're at it... I should mention that this problem only occurs if you are using temp tables and table variable. And then... not even all the time. Only if you are comparing string data.

For example....

Declare @Temp 
Table   (Id Int, Data VarChar(20) Collate [!]Latin1_General_CI_AS[/!])
Insert Into @Temp Values(1, 'ABC')
Insert Into @Temp Values(2, 'XYZ')

Declare @Temp2 Table(Id Int, Data VarChar(20) Collate [!]SQL_Latin1_General_CP1_CI_AS[/!])
Insert Into @Temp2 Values(1, 'abc')

[green]-- This works because we are comparing integers.[/green]
Select * 
From   @Temp As A 
       Inner Join @Temp2 As B 
         On [blue]A.Id = B.Id[/blue]

[green]-- This one fails because the collations are different.[/green]
Select * 
From   @Temp As A 
       Inner Join @Temp2 As B 
         On [blue]A.Data = B.Data[/blue]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLWilts]

Hi guys,
I know that this is an old post, but I really wanted to say something about collations - as in if you start changing the collation of a database, then you are digging yourself a very deep dark hole - yup, this is the voice of experience.

I am in the UK, which means that the default collation for SQL 2005 install is SQL_Latin1_General_CP1_CI_AS, whereby the majority of SQL 2000 server/database installs that I have come across (contracted for ages doing upgrades and conversions) are Latin1_General_CI_AS. And here the nighmare starts.

If you want to convert the server collation, then you will at first change the default collation on the server - great for all new databases created. However, when you come to change the collation of a database already attached... The collation is set at database level, table level and field level. I ended up writing a .net app to drill all the way down to field level and change the collation there.

If it helps even one person, take Georges idea of installing another instance on your server at the collation you need (providing disk space, cpu load and memory of course). You life will be much simpler - and there is no additional license fee to pay

 

[RyanEK]

Thanks for that SQLWilts, thats useful info.