Sorting by certain letter sequence

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

SQL Server 2008 Forums

Transact-SQL (2008)

Sorting by certain letter sequence

Author

Topic

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2011-11-09 : 09:26:05

I have a request to sort a particular field by a certain order of first letters then by the 2nd number.

For instance take these CarIDs:

A21222
A133224
D23223
B34434
B234334
Y34434

They want to sort by the following:

A,Y,D,B

and then sort from asc on the first digit

So in other words they want this as a result:

A133224
A21222
Y34434
D23223
B34434

I have no idea how to sort in a certain requested sequence on a letter like this, out of order (meaning not asc or desc...but some weird sequence).
B234334

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-11-09 : 09:46:15

Not sure it's the best approach and probably will be slow depending on the number of rows in the table...but

select * from yourtable
order by case when substring(carid,1,1) = 'A' then 1
when substring(carid,1,1) = 'Y' then 2
when substring(carid,1,1) = 'D' then 3
etc
etc
end,
substring(carid,2,1)

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2011-11-09 : 10:49:26

thanks. Here's what I tried but it's saying I'm still not including all the fields in the order by

[CODE]
select DISTINCT f.FoodNumber,
p.PartID,
fn.Name as FoodName,
d.[Description],
substring(n.Note, CHARINDEX(']', n.Note) + 2, LEN(n.Note)) as FoodNote
from Food f
join FoodName fn on fn.FoodNameID = f.FoodNameID
join FoodPart fp on fp.FoodID = p.FoodID
join [Application] a on a.ApplicationID = fn.ApplicationID
left join [Description] d on d.DescriptionID = ap.DescriptionID
join Note n on n.NoteID = a.NoteID
join FoodYear fy on fy.FoodYearID = a.FoodYearID
where mmy.FoodlId = 33997332
order by
case when when substring(n.Note, 1, 1) = 'A' then 1
when substring(n.Note, 1, 1) = 'Y' then 2
when substring(n.Note, 1, 1) = 'D ' then 3
END,
f.FoodNumber,
f.FoodID,
FoodName,
d.[description],
FoodNote
[/CODE]

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-11-09 : 11:12:37

Don't use the column alias in the order by, use the actual column name or expression.

order by
case when when substring(n.Note, 1, 1) = 'A' then 1
when substring(n.Note, 1, 1) = 'Y' then 2
when substring(n.Note, 1, 1) = 'D ' then 3
when substring(n.Note, 1, 1) = 'B ' then 4
END,
f.FoodNumber,
f.FoodID,
fn.Name,
d.[description],
substring(n.Note, CHARINDEX(']', n.Note) + 2, LEN(n.Note))

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2011-11-09 : 11:16:37

originally I didn't use the alias, that was just my latest attempt, it still doesn't work like this:

[CODE]
select DISTINCT f.FoodNumber,
p.PartID,
fn.Name as FoodName,
d.[Description],
substring(n.Note, CHARINDEX(']', n.Note) + 2, LEN(n.Note)) as FoodNote
from Food f
join FoodName fn on fn.FoodNameID = f.FoodNameID
join FoodPart fp on fp.FoodID = p.FoodID
join [Application] a on a.ApplicationID = fn.ApplicationID
left join [Description] d on d.DescriptionID = ap.DescriptionID
join Note n on n.NoteID = a.NoteID
join FoodYear fy on fy.FoodYearID = a.FoodYearID
where mmy.FoodlId = 33997332
order by
case when when substring(n.Note, 1, 1) = 'A' then 1
when substring(n.Note, 1, 1) = 'Y' then 2
when substring(n.Note, 1, 1) = 'D ' then 3
END,
f.FoodNumber,
f.FoodID,
fn.Name,
d.[description],
substring(n.Note, CHARINDEX(']', n.Note) + 2, LEN(n.Note))

[/CODE]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-09 : 11:20:48

the alias in the ORDER BY is not the issue. That is perfectly fine.

It is the column in the ORDER BY that is not in the SELECT clause. When you use a DISTINCT in the SELECT, all the column in the ORDER BY need to be in the SELECT clause

Remove the FoodID from the ORDER BY and try

KH
[spoiler]Time is always against us[/spoiler]

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2011-11-09 : 11:24:42

quote:
Originally posted by CoffeeAddict

originally I didn't use the alias, that was just my latest attempt, it still doesn't work like this:

[CODE]
select DISTINCT f.FoodNumber,
p.FoodID,
fn.Name as FoodName,
d.[Description],
substring(n.Note, CHARINDEX(']', n.Note) + 2, LEN(n.Note)) as FoodNote
from Food f
join FoodName fn on fn.FoodNameID = f.FoodNameID
join FoodPart fp on fp.FoodID = p.FoodID
join [Application] a on a.ApplicationID = fn.ApplicationID
left join [Description] d on d.DescriptionID = ap.DescriptionID
join Note n on n.NoteID = a.NoteID
join FoodYear fy on fy.FoodYearID = a.FoodYearID
where mmy.FoodlId = 33997332
order by
case when when substring(n.Note, 1, 1) = 'A' then 1
when substring(n.Note, 1, 1) = 'Y' then 2
when substring(n.Note, 1, 1) = 'D ' then 3
END,
f.FoodNumber,
f.FoodID,
fn.Name,
d.[description],
substring(n.Note, CHARINDEX(']', n.Note) + 2, LEN(n.Note))

[/CODE]

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2011-11-09 : 11:25:03

sorry I misposted last time, it's FoodID in the select and in the order by, still same error.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-09 : 11:25:37

that might not work also. You still need to settle the case when part.

Try this with a CTE


; with data as
(
        select 	DISTINCT 
		f.FoodNumber,
                p.PartID,
                fn.Name as FoodName,
                d.[Description],
                substring(n.Note, CHARINDEX(']', n.Note) + 2, LEN(n.Note)) as FoodNote,
		left(n.Note, 1) as SortChar
        from 	Food f
                join FoodName fn on fn.FoodNameID = f.FoodNameID
                join FoodPart fp on fp.FoodID = p.FoodID
                join [Application] a on a.ApplicationID = fn.ApplicationID
                left join [Description] d on d.DescriptionID = ap.DescriptionID
                join Note n on n.NoteID = a.NoteID
                join FoodYear fy on fy.FoodYearID = a.FoodYearID
        where 	mmy.FoodlId = 33997332
)
select	*
from	data
order by
        case when SortChar = 'A' then 1
             when SortChar = 'Y' then 2
             when SortChar = 'D ' then 3
             end,
        FoodNumber,
        FoodName,
        [description],
        FoodNote

KH
[spoiler]Time is always against us[/spoiler]

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2011-11-09 : 11:31:30

forgive me a CTE?

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-11-09 : 11:48:05

In the query what table is p.PartID from. I don't see the p alias in the table joins, is it supposed to be fp?

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2011-11-09 : 11:50:37

yea that's a mispost...should be FoodID I'm sorry...

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-11-09 : 11:50:48

quote:
Originally posted by khtan

the alias in the ORDER BY is not the issue. That is perfectly fine.

It is the column in the ORDER BY that is not in the SELECT clause. When you use a DISTINCT in the SELECT, all the column in the ORDER BY need to be in the SELECT clause

Remove the FoodID from the ORDER BY and try

KH
[spoiler]Time is always against us[/spoiler]

Silly me, I was thinking "group by" ... need more Coffee!!!