Order by clause in sql with starting character as new row

Question

1.00/5 (1 vote)

See more:

Hi all,

i want a query for Order by clause in sql with starting character as new row.

ColumnName
Austrilia
A1
Colombo
Brazil

OutPut:

ColumnName
A
A1
Austrilia
B
Brazil
C
Colombo

Select query for adding a new row with starting character "A,B,C,D etc"

Posted 19-Dec-13 2:31am

inayat basha

Add a Solution

2 solutions

Solution 2

Try this:

SQL

DECLARE @tmp TABLE (Country VARCHAR(30))

INSERT INTO @tmp (Country)
VALUES ('Austrilia'), ('A1'), ('Colombo'), ('Brazil')

SELECT Country
FROM (
    SELECT Country
    FROM @tmp
    UNION ALL
    SELECT LEFT(Country,1) AS Country
    FROM @tmp
) AS T
ORDER BY Country

Posted 19-Dec-13 8:17am

Maciej Los

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

coded007 · Accepted Answer · 2013-12-19T03:41:00

SQL

WITH CTE
AS
(
    Select 'Austrilia' as Country
    UNION
    Select 'A1' as country
    UNION
    Select 'Colombo' as country
    UNION
    SELECT 'Brazil' as country
),

CTEModified
AS
(
    Select SUBSTRING(country, 1, 1) as country FROM CTE
    Union
    SElect country from CTE
)

SELECT * FROM CTEModified

Order by clause in sql with starting character as new row

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Order by clause in sql with starting character as new row

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us