order by query

Hi, I am using below query:

SELECT tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy, tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract, tbh_Articles.Body,
tbh_Articles.Country, tbh_Articles.State, tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate, tbh_Articles.Approved, tbh_Articles.Listed,
tbh_Articles.CommentsEnabled, tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes, tbh_Articles.TotalRating,
tbh_Articles.ImageURL, tbh_Articles.special
FROM tbh_Lang CROSS JOIN
tbh_Articles
WHERE (tbh_Lang.LangID = @.LanguageID) AND (tbh_Articles.ArticleID = tbh_Lang.ArticleMain OR
tbh_Articles.ArticleID = tbh_Lang.ArticleSecond1 OR
tbh_Articles.ArticleID = tbh_Lang.ArticleSecond2 OR
tbh_Articles.ArticleID = tbh_Lang.ArticleSecond3 OR
tbh_Articles.ArticleID = tbh_Lang.ArticleSecond4 OR
tbh_Articles.ArticleID = tbh_Lang.ArticleSecond5)

Problem is that I want to sort in a manner which the results returns as ArticleMain as the first column, ArticleSecond1 as the second and so on...

Tables structure: tbh_Articles(id, title, body...) ; tbh_Lang(id,ArticleMain,ArticleSecond1 ,ArticleSecond2... )

Any suggestions?

Try adding an ORDER BY clause after your WHERE clause and put the columns on which you want to order by.

|||

Tried that. that was my first instinct. the problem is that the query looks for all the articleIDs that in the sub query...it doesnt matter what is the order in the subquery.

Any other ideas?

Actually now I am getting punisg for using unormalize tables...

|||

I dont see any subquery in the code you provided. can you provide some sample data and how you want it sorted on?

|||

you need to ORDER BY tbh_Lang.ArticleMain, tbh_Lang.ArticleSecond1 , etc

Put the table qualifier (tbh_Lang) in the ORDER BY column specification.

Also, anything you put in the ORDER BY should also be in the select list

If you tried this, and it didn't work, then you didn't do what you think you did. This will work.

|||

dbland07666:

you need to ORDER BY tbh_Lang.ArticleMain, tbh_Lang.ArticleSecond1 , etc

Put the table qualifier (tbh_Lang) in the ORDER BY column specification.

Also, anything you put in the ORDER BY should also be in the select list

If you tried this, and it didn't work, then you didn't do what you think you did. This will work.

No.

Perhaps.

No.

Or perhaps you don't fully understand the problem. (I might not either) ndinakar asked for clarification, which I suspect will show that what you are suggesting doesn't do what he wants. I'll take a guess, but it's only a guess at this point.

|||

TRY:

ORDER BY tbh_Lang.ArticleMain,CASE WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleMain THEN 0

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond1 THEN 1

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond2 THEN 2

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond3 THEN 3

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond4 THEN 4

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond5 THEN 5

ELSE 9 END

Is that what you were looking for?

|||

megetron:

Problem is that I want to sort in a manner which the results returns as ArticleMain as the first column, ArticleSecond1 as the second and so on...

I find that sentence confusing.

I wondered: Do you mean that you want to SORT by ArticleMain, ArticleSecond1, 2, 3 ... - ie put rows in that order without including all those columns in the results -

or do you mean you want to "return" those columns as in, fetch them as part of the results rows?

Then I wondered if you meant that you wanted to sort by only one of ArticleSecond1 or ArticleSecond2 etc, etc, whichever one of them happened to match ArticlesID.

But that is an uneccessary complication, because whichever one it was, it matches ArticlesID, so we can simply sort by ArticlesID instead.

So, I am not sure what you really want to happen, but perhaps you can clarify that for us.

|||

Sorry for the late response,
It looks like my question was not clear enough

I needed to sort the articles according to the data I have in ArticleMain, articleSecond1, areticleSecond2 and so on...

if the value in the tbh_lang table will be ArticleMain=5, ArticleSecond1=2 and ArticleSecond2=6 then the query will return from the Articles table the data details for article id 5, 2 , 6 and exactly in this order...currently the above query returns: 2,5,6 which is the order of the ids in the article table.

The following query do this but it is not that effisient. I am paying for the fact that I didn;t normalize tables, but still here is the query (if you can find any better, please post):

SELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesWHERE (ArticleIDIN (SELECT ArticleMainFROM tbh_LangWHERE (LangID = @.LanguageID)))UNIONALLSELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesAS tbh_Articles_5WHERE (ArticleIDIN (SELECT ArticleSecond1FROM tbh_LangAS tbh_Lang_5WHERE (LangID = @.LanguageID)))UNIONALLSELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesAS tbh_Articles_4WHERE (ArticleIDIN (SELECT ArticleSecond2FROM tbh_LangAS tbh_Lang_4WHERE (LangID = @.LanguageID)))UNIONALLSELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesAS tbh_Articles_3WHERE (ArticleIDIN (SELECT ArticleSecond3FROM tbh_LangAS tbh_Lang_3WHERE (LangID = @.LanguageID)))UNIONALLSELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesAS tbh_Articles_2WHERE (ArticleIDIN (SELECT ArticleSecond4FROM tbh_LangAS tbh_Lang_2WHERE (LangID = @.LanguageID)))UNIONALLSELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesAS tbh_Articles_1WHERE (ArticleIDIN (SELECT ArticleSecond5FROM tbh_LangAS tbh_Lang_1WHERE (LangID = @.LanguageID)))

|||

Use your original query with this added at the end:

ORDER BY CASE WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleMain THEN 0

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond1 THEN 1

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond2 THEN 2

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond3 THEN 3

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond4 THEN 4

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond5 THEN 5

ELSE 9 END

PS. Your union query is close, but it doesn't guarantee the order of the results. It may work most of the time, but 'A' UNION ALL 'B' can come back in any order. If you want to use your union idea, this is how you need to guarantee order:

SELECT *

FROM (

SELECT 1 AS SortOrder,ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURL
FROM tbh_Articles
WHERE (ArticleIDIN
(SELECT ArticleMain
FROM tbh_Lang
WHERE (LangID = @.LanguageID)))

UNION ALL

SELECT 2 AS SortOrder,ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURL
FROM tbh_Articles
WHERE (ArticleIDIN
(SELECT ArticleSecond1
FROM tbh_Lang
WHERE (LangID = @.LanguageID)))

...

) t1

ORDER BY SortOrder

And here's a 3rd method that should work as well:

SELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURL
FROM tbh_Articles
JOIN (SELECT 1 AS SortOrder, ArticleMain AS AID
FROM tbh_Lang
WHERE (LangID = @.LanguageID)

UNION ALL

SELECT 2 AS SortOrder, ArticleSecond1 AS AID
FROM tbh_Lang
WHERE (LangID = @.LanguageID)

UNION ALL

...

) InnerTable ON Innertable.AID=tbh_Articles.ArticleID

ORDER BY InnerTable.SortOrder