sql server ordering the data

Question

0.00/5 (No votes)

See more:

Hi all,
I have one table like

SQL

create table #table1(id int,name varchar(40))
insert into #table1
select 15 id,'Global' name
union
select 16 id,'Liberty' name
union
select 17 id,'Noble' name

and i have another table like

SQL

create table #table2(number int,id int,name varchar(50))
insert into #table2
select number=1,id=15,'James Monroe'name
union
select number=2,id=15,'Cornell'name
union
select number=3,id=15,'Abraham Lincoln'name

union
select number=4,id=16,'James K. Polk'name
union
select number=5,id=16,'Roosevelt'name
union
select number=6,id=16,'George Washington'name

union
select number=7,id=17,'Woodrow Wilson'name
union
select number=8,id=17,'Andrew Jackson'name
union
select number=9,id=17,'John Adams'name

i need to print the data like this

VB

15,'Global'
3,'Abraham Lincoln'
2,'Cornell'
1,'James Monroe'
16 ,'Liberty'
6,'George Washington'
4,'James K. Polk'
5,'Roosevelt'
17,'Noble'
8,'Andrew Jackson'
9,'John Adams'
7,'Woodrow Wilson'

Posted 11-Nov-13 22:14pm

prakash.chakrala

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Kornfeld Eliyahu Peter · Answer 1 · 2013-11-11T22:27:00

Solution 1

SQL

create table #res(number int, id int,name varchar(40))

insert into #res
	select null as number, id, name from #table1
	union
	select number, id, name from #table2

select 
	case when number is null then id else number end,
	name
from #res
order by id, number

Posted 11-Nov-13 22:27pm

Kornfeld Eliyahu Peter

**Zoltán Zörgő** · Answer 2 · 2013-11-11T22:40:00

Solution 2

This will also work:

SQL

select t2.number, t2.name from #table1 t1 left join
(
select id, name, 0 as o, id as number from #table1 union select id, name, 1 as o, number from #table2
)
t2 on t1.id=t2.id
order by t1.id, o, number desc

Posted 11-Nov-13 22:40pm

Zoltán Zörgő

Comments

prakash.chakrala 12-Nov-13 5:25am

i need to sort the data based on name

Zoltán Zörgő 12-Nov-13 5:31am

Well, than change the last part with this:
order by t1.id, o, t2.name

prakash.chakrala 12-Nov-13 5:40am

not getting the output as expected

Zoltán Zörgő 12-Nov-13 5:43am

You should. This is what I got:

15	Global
3	Abraham Lincoln
2	Cornell
1	James Monroe
16	Liberty
6	George Washington
4	James K. Polk
5	Roosevelt
17	Noble
8	Andrew Jackson
9	John Adams
7	Woodrow Wilson

And looks like what you expect.

prakash.chakrala 12-Nov-13 5:50am

am getting the output like
15 Global
3 Abraham Lincoln
2 Cornell
1 James Monroe
16 Liberty
6 George Washington
5 Roosevelt
4 James K. Polk
17 Noble
9 John Adams
8 Andrew Jackson
7 Woodrow Wilson

Zoltán Zörgő 12-Nov-13 5:55am

Well, than you have some strange collation. Check database collation, or add collation to order by clause.

prakash.chakrala 12-Nov-13 6:01am

iam getting following collation:SQL_Latin1_General_CP1_CI_AS
may i know which collation you are getting.

Zoltán Zörgő 12-Nov-13 6:51am

Well, this code is running on my side as expected:


drop table #table1;
drop table #table2;

create table #table1(id int,name varchar(40))
insert into #table1
select 15 id,'Global' name
union
select 16 id,'Liberty' name
union
select 17 id,'Noble' name
 

create table #table2(number int,id int,name varchar(50))
insert into #table2
select number=1,id=15,'James Monroe'name
union
select number=2,id=15,'Cornell'name
union
select number=3,id=15,'Abraham Lincoln'name
 
union
select number=4,id=16,'James K. Polk'name
union
select number=5,id=16,'Roosevelt'name
union
select number=6,id=16,'George Washington'name
 
union
select number=7,id=17,'Woodrow Wilson'name
union
select number=8,id=17,'Andrew Jackson'name
union
select number=9,id=17,'John Adams'name

select t2.number, t2.name from #table1 t1 left join
(
select id, name, 0 as o, id as number from #table1 union select id, name, 1 as o, number from #table2
)
t2 on t1.id=t2.id
order by t1.id, o, t2.name COLLATE Latin1_General_CS_AS