Order by letters then numbers, by row

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

General SQL Server Forums

New to SQL Server Programming

Order by letters then numbers, by row

Author

Topic

Matt5092
Starting Member

6 Posts

Posted - 2010-02-10 : 05:46:14

Hi there,

I'm quite new to SQL and am stumped by what I hope is a simple query.

I have 3 tables that link to eath other with an inner join, with a distinct to remove duplicates.

SELECT DISTINCT dbo.Establishments.PropertyName, dbo.Buildings.BuildingName, dbo.Rooms.FloorLevel
FROM         dbo.Establishments INNER JOIN
                      dbo.Buildings ON dbo.Establishments.EN = dbo.Buildings.EN INNER JOIN
                      dbo.Rooms ON dbo.Establishments.EN = dbo.Rooms.EN
ORDER by dbo.Establishments.PropertyName, dbo.Buildings.BuildingName, dbo.Rooms.FloorLevel

All I would like to do is order by the letter in the FloorLevel column first, so they would be grouped

G
0
1
2
3

Is this possible?

Many thanks,

msuneerbabu
Starting Member

6 Posts

Posted - 2010-02-10 : 05:52:42

Order by floorlevel

SELECT DISTINCT dbo.Establishments.PropertyName, dbo.Buildings.BuildingName, dbo.Rooms.FloorLevel
FROM dbo.Establishments INNER JOIN
dbo.Buildings ON dbo.Establishments.EN = dbo.Buildings.EN INNER JOIN
dbo.Rooms ON dbo.Establishments.EN = dbo.Rooms.EN
ORDER by dbo.Rooms.FloorLevel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 06:06:54

[code] Order BY CASE WHEN ISNUMERIC(Val) = 0 THEN 0 ELSE 1 END,Val[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP

Matt5092
Starting Member

6 Posts

Posted - 2010-02-10 : 09:36:00

Thanks for those, although it needs to be ordered by the first 2 columns first.

Visakh, How would I integrate your suggestion into my query? It doesn't seem to work with the distinct in the select.

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Many thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 09:36:47

quote:
Originally posted by Matt5092

Thanks for those, although it needs to be ordered by the first 2 columns first.

Visakh, How would I integrate your suggestion into my query? It doesn't seem to work with the distinct in the select.

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Many thanks,

show your query please

------------------------------------------------------------------------------------------------------
SQL Server MVP

Matt5092
Starting Member

6 Posts

Posted - 2010-02-11 : 04:54:58

[code]SELECT DISTINCT dbo.Establishments.PropertyName, dbo.Buildings.BuildingName, dbo.Rooms.FloorLevel
FROM dbo.Establishments INNER JOIN
dbo.Buildings ON dbo.Establishments.EN = dbo.Buildings.EN INNER JOIN
dbo.Rooms ON dbo.Establishments.EN = dbo.Rooms.EN
ORDER by dbo.Establishments.PropertyName, dbo.Buildings.BuildingName, CASE WHEN ISNUMERIC(dbo.Rooms.FloorLevel) = 0 THEN 0 ELSE 1 END,dbo.Rooms.FloorLevel[/code]

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 04:56:22

quote:

Originally posted by Matt5092

SELECT DISTINCT dbo.Establishments.PropertyName, dbo.Buildings.BuildingName, dbo.Rooms.FloorLevel
FROM dbo.Establishments INNER JOIN
dbo.Buildings ON dbo.Establishments.EN = dbo.Buildings.EN INNER JOIN
dbo.Rooms ON dbo.Establishments.EN = dbo.Rooms.EN
ORDER by dbo.Establishments.PropertyName, dbo.Buildings.BuildingName, CASE WHEN ISNUMERIC(dbo.Rooms.FloorLevel) = 0 THEN 0 ELSE 1 END,dbo.Rooms.FloorLevel

Thanks,

why do you need DISTINCT here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Matt5092
Starting Member

6 Posts

Posted - 2010-02-11 : 05:59:35

The relationship is one-to-many, shown by adding the ID column from the rooms table.

Is it possible to have a single entry (after removing the ID column)?

Many thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 06:02:40

if yoou dont want id isnt this enough?


SELECT DISTINCT dbo.Establishments.PropertyName, dbo.Buildings.BuildingName, dbo.Rooms.FloorLevel
FROM dbo.Establishments INNER JOIN
dbo.Buildings ON dbo.Establishments.EN = dbo.Buildings.EN INNER JOIN
dbo.Rooms ON dbo.Establishments.EN = dbo.Rooms.EN
GROUP BY dbo.Establishments.PropertyName, dbo.Buildings.BuildingName, dbo.Rooms.FloorLevel
ORDER by dbo.Establishments.PropertyName, dbo.Buildings.BuildingName, CASE WHEN ISNUMERIC(dbo.Rooms.FloorLevel) = 0 THEN 0 ELSE 1 END,dbo.Rooms.FloorLevel

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources