Please help me with this select

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 2000 Forums

Transact-SQL (2000)

Please help me with this select

Author

Topic

valerod
Starting Member

9 Posts

Posted - 2002-11-07 : 09:28:11

Hi.
I have one table.
cod | desc
1 | aaa
2 | ccc
3 | xxx
4 | bbb
I need a select statement that put the row 3 in the top off the list and the sort order rows by desc column.Ex:
cod | desc
3 | xxx
1 | aaa
4 | bbb
2 | ccc
How can I do that?
Thanks
Valerod

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-07 : 09:34:30

Make a temporary table which consists of cod and desc and a third field to order everything by, then insert the values from the original table into the temporary table, then pull everything from the temporary table.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-07 : 10:02:21

select * from tbl
order by case when [desc] = 'xxx' then 0 else 1 end, [desc]

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 11/07/2002 10:02:59

valerod
Starting Member

9 Posts

Posted - 2002-11-07 : 10:22:09

Thanks a lot

valerod
Starting Member

9 Posts

Posted - 2002-11-07 : 10:44:06

Thanks nr .
Just on more question.
Can I put the 3º row 2 times in the fetch? In the top and sorted.
Ex:
cod | desc
1 | aaa
2 | ccc
3 | xxx
4 | bbb

cod | desc
3 | xxx
1 | aaa
4 | bbb
2 | ccc
3 | xxx
Thanks
Valerod

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-07 : 12:26:20

not very nice

select cod, desc
from (select srt = 1, * from tbl union select 1, * from tbl where cod = 3) as a
order by srt, desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

valerod
Starting Member

9 Posts

Posted - 2002-11-07 : 14:01:47

I dont understant

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-07 : 14:29:05

oops

select cod, desc
from (select srt = 1, * from tbl union select 0, * from tbl where cod = 3) as a
order by srt, desc

derived table with your row twice - once with srt = 0 to come at the top and again with same as other rows to come in order.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

valerod
Starting Member

9 Posts

Posted - 2002-11-08 : 07:02:11

Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources