select items from table and order by an array? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

select items from table and order by an array?

Author

Topic

dukevn
Starting Member

5 Posts

Posted - 2008-07-14 : 09:16:31

Hi folks,

I have a table like:

id item_name

1 test1
2 test2
3 test3
4 test4
...

and I want to select, say 3 items (1,3,4) from that table in the order that I want (for example, 4,1,3). If I use:

SELECT id,item_name WHERE (id=4 OR id=1 OR id=3)

I will get an array contains 3 items in the order of (1,3,4), not that I want.

How I can select those 3 items with my own order? Is it possible? I check syntax of SELECT with ORDER BY, but it only shows ordering by id or by item_name, not by an array that I can define my own.

Thanks,

D.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-14 : 09:41:44

Here's one option...


--Data
declare @MyTable table (id int, item_name varchar(10))
insert @MyTable
          select 1, 'test1'
union all select 2, 'test2'
union all select 3, 'test3'
union all select 4, 'test4'

--Inputs
declare @IdsWanted table (MyOrder int identity(1, 1), id int)
insert @IdsWanted select 4 union all select 1 union all select 3

--Query
select a.* from @MyTable a inner join @IdsWanted b on a.id = b.id order by b.MyOrder

/* Results
id          item_name
----------- ----------
4           test4
1           test1
3           test3
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 09:42:44

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106027

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-14 : 09:45:30

or

select * from @MyTable where (id=4 OR id=1 OR id=3)
order by case when id=4 then 1 when id=1 then 2 when id=3 then 3 end

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-14 : 09:48:16

quote:
Originally posted by khtan

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106027

KH
[spoiler]Time is always against us[/spoiler]

there

Madhivanan

Failing to plan is Planning to fail

dukevn
Starting Member

5 Posts

Posted - 2008-07-14 : 10:57:03

quote:

Originally posted by RyanRandall

Here's one option...


--Data
declare @MyTable table (id int, item_name varchar(10))
insert @MyTable
          select 1, 'test1'
union all select 2, 'test2'
union all select 3, 'test3'
union all select 4, 'test4'

--Inputs
declare @IdsWanted table (MyOrder int identity(1, 1), id int)
insert @IdsWanted select 4 union all select 1 union all select 3

--Query
select a.* from @MyTable a inner join @IdsWanted b on a.id = b.id order by b.MyOrder

/* Results
id          item_name
----------- ----------
4           test4
1           test1
3           test3
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Sorry if I am too noob :), but to my understand, the solution is to create another table with desired order, and then select items in old table based on new table?

D.

dukevn
Starting Member

5 Posts

Posted - 2008-07-14 : 11:03:33

quote:
Originally posted by khtan

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106027

KH
[spoiler]Time is always against us[/spoiler]

Thanks for the link. Do not know that people have the same issue but ask in a different way :).

The main key in the solution is to create the function to split the desired array and use that in the syntax. I am working with mySQL, and I have to write a php code to archive that. With a very limited knowledge in SQL syntax, I think this method is beyond my ability. I will learn more to understand and implement that, but meanwhile, is there a simpler solution?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 11:05:44

quote:
Originally posted by dukevn

I am working with mySQL

then you have posted in the wrong forum. This is a Microsoft SQL Server forum.

KH
[spoiler]Time is always against us[/spoiler]

dukevn
Starting Member

5 Posts

Posted - 2008-07-14 : 11:07:12

quote:
Originally posted by madhivanan

or

select * from @MyTable where (id=4 OR id=1 OR id=3)
order by case when id=4 then 1 when id=1 then 2 when id=3 then 3 end

Madhivanan

Failing to plan is Planning to fail

This is quite handy solution, but while those other solutions are beyond my knowledge, this seems... understandable and doable for me. The only downside is that you will need to know the desired array length.

Thanks.

dukevn
Starting Member

5 Posts

Posted - 2008-07-14 : 12:17:09

quote:
Originally posted by khtan

quote:
Originally posted by dukevn

I am working with mySQL

then you have posted in the wrong forum. This is a Microsoft SQL Server forum.

KH
[spoiler]Time is always against us[/spoiler]

Honestly, I do not know this is just for Miscrosoft SQL Server people. I thought this is a forum about SQL and SQL syntax, and mySQL would do the same thing :).

Anyway, thanks a lot guys for quick replies. At least I learn something from you.

D.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 03:07:38

For mysql questions post at www.mysql.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources