First on the list - SQL Server Forums

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

First on the list

Author

Topic

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-11-14 : 00:13:32

Hi,

I have a table but I want to have one column to be the first on the list. I was trying to use the Group By function, but still didn't work.

Thanks

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-14 : 00:37:31

what do you mean by FIRST ON THE LIST ???

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-11-14 : 00:41:50

i was using order by..i want a particular row to be first on the list

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 00:42:11

do you mean ordering on a field? or order based on grouped aggregate like sumj,average...?please specify what you want with some sample data

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-14 : 00:42:15

we can not move the column from any other position to first position using code.
but we can do only using UI(if sql server 2005)

malay

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-11-14 : 00:48:26

I have column:

Apple
Cat
Dog

I want cat to be the first on the list. How can I can do that by using order by ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 00:51:45

[code]select *
from
(select 'Apple',2 as orderval
union all
select 'Cat',1
union all
select 'Dog',3)t
order by orderval[/code]

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-14 : 00:59:02

quote:

Originally posted by visakh16

select *
from
(select 'Apple',2 as orderval
union all
select 'Cat',1
union all
select 'Dog',3)t
order by orderval

this is showing some error message
: No column was specified for column 1 of 't'.

malay

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-11-14 : 01:09:27

what if the table does have 1000 rows?

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-14 : 01:12:00

Actually what is your requirement?

If u want to order in ur own way how many column there may be in the table .U can write stored procedure with parameter as column name.

malay

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 01:26:47

order by case when col='Cat' then 0 else 1 end, col

Madhivanan

Failing to plan is Planning to fail

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-11-14 : 02:22:42

It didn't work. Here's my statement:

SELECT '' AS ChildID, Coach, 'All' AS EmpName
FROM dbo.TPNEWLevel2Child
UNION ALL
SELECT *
FROM dbo.TPNEWLevel2Child

I want to distinct All

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 02:42:49

quote:
Originally posted by BankOfficerHere

It didn't work. Here's my statement:

SELECT '' AS ChildID, Coach, 'All' AS EmpName
FROM dbo.TPNEWLevel2Child
UNION ALL
SELECT *
FROM dbo.TPNEWLevel2Child

I want to distinct All

if you want distinct use UNION instead of union all

also dont use * in second select. please use fieldnames and make sure number of and datatype of fields used on both sides of UNION are same.

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-11-14 : 02:44:54

how can i do that?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 03:02:59

[code]
select * from
(
SELECT '' AS ChildID, Coach, 'All' AS EmpName, 1 as col_order
FROM dbo.TPNEWLevel2Child
UNION ALL
SELECT col1,col2,, 2 as col_order
FROM dbo.TPNEWLevel2Child
) as t
order by col_order
[/code]

Madhivanan

Failing to plan is Planning to fail

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2008-11-14 : 03:21:19

ok but still i want to distinct All. How can I do that?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 03:57:23

quote:
Originally posted by BankOfficerHere

ok but still i want to distinct All. How can I do that?


select * from
(
	SELECT '' AS ChildID,  'All' AS EmpName, 1 as col_order
	UNION ALL
	SELECT col1,col2, 2 as col_order
	FROM dbo.TPNEWLevel2Child
) as t
order by col_order

Madhivanan

Failing to plan is Planning to fail

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-11-17 : 05:23:37

Use Row_number() in the select List, it will be easier

hi this is nag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-17 : 07:14:18

quote:
Originally posted by Nageswar9

Use Row_number() in the select List, it will be easier

hi this is nag

Can you show the example?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources