Order by field value - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Order by field value

Author

Topic

cool_moon
Starting Member

26 Posts

Posted - 2007-07-30 : 04:38:12

Hi,

Can we use "Order by" to return result depending upon by field value.

Example:
Id Month
1 January
2 February
3 March
4 April
5 May

Now what i want is:

2 February
5 May
4 April
3 March
1 January

I'll always be getting data of February first, May second, April third and so on...so i can define it to my query if it helps.

Rit now i can only order by field i.e. "Id" or "Month" but cannot order by field value i.e. "Month values".

Can we do it by anychance?

Many thanks
cooool

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-30 : 04:41:10

add your custom order to another table with the month id, join the original table to the new
table and order by your custom id.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-07-30 : 04:52:38

SELECT Id, [Month]
FROM Table
ORDER BY
CASE Id
WHEN 1 THEN 5
WHEN 2 THEN 1
WHEN 3 THEN 4
WHEN 4 THEN 3
WHEN 5 THEN 2
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 04:58:10

What is "month values"? Are you using the table above in a JOIN for a query and you want to sort by SUM per month?
Please post full query before we can help you.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 04:59:57

Here is another trick you can use to sort "month names only"

-- Prepare sample data
DECLARE	@Untouchable TABLE (ID INT, [Month] VARCHAR(13))

INSERT	@Untouchable
SELECT	4, 'March' UNION ALL
SELECT	3, 'April' UNION ALL
SELECT	1, 'February' UNION ALL
SELECT	5, 'January' UNION ALL
SELECT	2, 'May'

-- Do the magic
SELECT		ID,
		[Month]
FROM		@Untouchable
ORDER BY	CAST([Month] + ' 2007' AS DATETIME)

E 12°55'05.25"
N 56°04'39.16"

cool_moon
Starting Member

26 Posts

Posted - 2007-07-30 : 06:39:24

Thanks for your replies:

Let me paste my query for your reference:

Following is the store procedure:

===================================

EXECUTE('SELECT ID,
ChannelId,
Date,
Time,
Duration,
Category,
TitleEn,
TitleAr,
GenreEn,
GenreAr,
EpisodeEn,
EpisodeAr
FROM
' + @TableOne + '
WHERE
(GenreEn IN (''COM'',''SPO'',''ACT'', ''ENT'')) AND
(ChannelId IN ('+@Channel+')) AND
(TitleEn = '''+@Filter +''') AND
(Date >= '''+@EventDate+''') AND
(Time >= '''+@EventTime+''') AND
(Category = ''H'')
UNION
SELECT ID,
Channel_name as ChannelId,
event_date as Date,
start as Time,
Duration,
NULL AS Category,
title as TitleEn,
arab_title as TitleAr,
Genre As GenreEn,
NULL As GenreAr,
log as EpisodeEn,
arabic_log as EpisodeAr
FROM
' + @TableTwo + '
WHERE
(Genre IN (''Sport'',''Entertainment'',''Movie'')) AND
(Channel_name IN ('+@Channel+')) AND
(title = '''+@Filter +''') AND
(event_date >= '''+@EventDate+''') AND
(start >= '''+@EventTime+''')
Order by Date,Time,ChannelId
')

===================================

Here i am getting data from TWO TABLES by using UNION.

I am getting all the data from both tables fine.

But in my cast, I not only have to get data from both tables but also have to sort data according to specific order. Let me give you an example:

Time Event Channel Name
11:00 Team1 v Team 2 Channel 2
11:00 Team 3 v Team 4 Channel 1
11:00 Team 5 v Team 6 Channel 3
12:00 Team 7 v Team 8 Channel 4

Now what is my requirement is to show records as:

Time Event Channel Name
11:00 Team 3 v Team 4 Channel 1
11:00 Team1 v Team 2 Channel 2
11:00 Team 5 v Team 6 Channel 3
12:00 Team 7 v Team 8 Channel 4

Here i am sorting record by channel. But problem is when the Time is common for more then one channel.
So first Order By is:Date,
second order by is:Time,
Third Order by is:Channel and
then if Date/Time is same and the channels are deferent, then sort it by channel so that every time Channel1 will come first then Channel 2 and so on.

I hope i am making any sense.

Thanks
cooooooool

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 06:44:31

ORDER BY 3, 4, 2 maybe?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 06:49:13

Or, post full SP so we know what the parameters are.

E 12°55'05.25"
N 56°04'39.16"

cool_moon
Starting Member

26 Posts

Posted - 2007-07-30 : 07:00:18

Here you go buddy:

============================

EXECUTE('SELECT ID,
ChannelId,
Date,
Time,
Duration,
Category,
TitleEn,
TitleAr,
GenreEn,
GenreAr,
EpisodeEn,
EpisodeAr
FROM
PROGRAMTABLE_ONE
WHERE
(GenreEn IN (''COM'',''SPO'',''ACT'', ''ENT'')) AND
(ChannelId IN (''Channel1','Channel2,'Channel3','Channel4'')) AND
(TitleEn = ''''Live Programs'''') AND
(Date >= '''30/07/2008''') AND
(Time >= '''03:00''') AND
(Category = ''H'')
UNION
SELECT ID,
Channel_name as ChannelId,
event_date as Date,
start as Time,
Duration,
NULL AS Category,
title as TitleEn,
arab_title as TitleAr,
Genre As GenreEn,
NULL As GenreAr,
log as EpisodeEn,
arabic_log as EpisodeAr
FROM
PROGRAMTABLE_TWO
WHERE
(Genre IN (''Sport'',''Entertainment'',''Movie'')) AND
(Channel_name IN ('''Channel1','Channel2,'Channel3','Channel4'')) AND
(title = ''''Live Programs'''') AND
(event_date >= ''''30/07/2008'''') AND
(start >= '''03:00''')
Order by Date,Time,ChannelId
')

============================

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 07:08:50

Did you try ORDER BY 3, 4, 2?
Also please post some sample data from your DATE column and TIME column.

E 12°55'05.25"
N 56°04'39.16"

cool_moon
Starting Member

26 Posts

Posted - 2007-07-30 : 07:20:01

Yes...gave me this error.:
When i try Order By Channel1,Channel2,Channel3
Error:
Invalid column name 'Channel1'.

When i try Order By 'Channel1','Channel2','Channel3'
Error:
ORDER BY items must appear in the select list if the statement contains a UNION operator.

cool_moon
Starting Member

26 Posts

Posted - 2007-07-30 : 07:30:30

Sample Data:

Channel_Name Date Time Title Genre Event
Channel4 2007-08-11 11:45:00.000 Live Event SPO Team1 V Team2
Channel1 2007-08-11 14:00:00.000 Live Event Movie Team3 V Team4
Channel3 2007-08-11 14:00:00.000 Live Event Sport Team5 V Team6
Channel2 2007-08-11 14:00:00.000 Live Event Entertainment Team7 V Team8

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 07:46:42

When did ORDER BY 3, 4, 2 become the same as ORDER BY Channel3, Channel4, Channel2???
The 3, 4 and 2 is the ORDINAL NUMBER of which columns to sort.

Do an "ORDER BY 3, 4, 2" and nothing more.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 07:50:28

Which is it? In your sample data above, there are no separate date and time columns!

Channel_Name  Date Time                Title       Genre          Event
------------  -----------------------  ----------  -------------  -------------
Channel4      2007-08-11 11:45:00.000  Live Event  SPO            Team1 V Team2
Channel1      2007-08-11 14:00:00.000  Live Event  Movie          Team3 V Team4
Channel3      2007-08-11 14:00:00.000  Live Event  Sport          Team5 V Team6
Channel2      2007-08-11 14:00:00.000  Live Event  Entertainment  Team7 V Team8

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 07:53:57

Why do you refuse to help us helping you?

1) Post your table layout for the two tables.
2) Post some proper sample data that is stored in your two tables.
3) Post the expected output based on the provided sample data

Look at this post for knowing how to post a proper question.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81373

E 12°55'05.25"
N 56°04'39.16"

cool_moon
Starting Member

26 Posts

Posted - 2007-07-30 : 11:25:01

Thanks Peso...for your help as well as advice...really appreciated

“spirit1”: You rocks buddy. I have created a new table and use the join query to sort out record…and it seems to be working fine.

Here is the final sample SQL query

==================================

EXECUTE('
SELECT * from(
SELECT ID,
ChannelId,
Date,
Time,
Duration,
Category,
TitleEn,
TitleAr,
GenreEn,
GenreAr,
EpisodeEn,
EpisodeAr
FROM
PROGRAM_TABLE_ONE
WHERE
(GenreEn IN (''COM'',''SPO'',''ACT'', ''ENT'')) AND
(ChannelId IN (''Channel1','Channel2,'Channel3','Channel4'')) AND
(TitleEn = ''''Live Programs'''') AND
(Date >= '''30/07/2008''') AND
(Time >= '''03:00''') AND
(Category = ''H'')
UNION
SELECT ID,
Channel_name as ChannelId,
event_date as Date,
start as Time,
Duration,
NULL AS Category,
title as TitleEn,
arab_title as TitleAr,
Genre As GenreEn,
NULL As GenreAr,
log as EpisodeEn,
arabic_log as EpisodeAr
FROM
PROGRAM_TABLE_TWO
WHERE
(Genre IN (''Sport'',''Entertainment'',''Movie'')) AND
(Channel_name IN ('''Channel1','Channel2,'Channel3','Channel4'')) AND
(title = ''''Live Programs'''') AND
(event_date >= ''''30/07/2008'''') AND
(start >= '''03:00''')
)q
JOIN PROGRAM_ORDERBY_TABLE as C
ON q.ChannelId = [C].[Channel Code]
WHERE ([C].[Channel Code] IN (''Channel1','Channel2,'Channel3','Channel4'')))
order by q.Date,q.Time, c.Priority
')

==================================

Thanks
Regards
coooool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources