homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Sorting by multiple, weighted columns
instead of by one column after the other

 3:33 pm on May 19, 2011 (gmt 0)

I consider myself pretty good at writing SQL queries, but there's one thing I've never figured out how to do.

I need to sort records by multiple columns and not just by one column after the other. This has got to be a common practice, but I can't even find the right words to search for to figure out how to do this.

In other words, an 'ORDER BY ColumnA, ColumnB' will list records in order of ColumnA and use ColumnB only as a tie breaker if more than one record has the same value in ColumnA. This won't work for what I'm trying to do.

I need to be able to provide search criteria for ColumnA and ColumnB and retrieve a list of records which most closely match both columns, and preferably be able to specify a weighting for whichever column is most important.

Can anyone point me in the right direction for this?



 5:05 pm on May 19, 2011 (gmt 0)

I can't locate the thread ATM, but you can sort by an expression. The example that comes to mind,

select title,description from table order by sequence>0 desc, sequence asc

The reason for this query was the client said, "it should only bring them to the top if I enter a number." Even if 0 < 1, this will bring any non-zero numbers to the top.

So you can start poking around with an expression of some sort, a starter:

select fields from table [where clause] sort by ColumnB > ColumnA desc, ColumnA > ColumnB desc

That probably won't work but you get the idea, you can sort by an expression on the two columns.


 11:08 pm on May 19, 2011 (gmt 0)

That's it! That is the answer I've been looking for, for over a decade!

Thanks, rocknbil.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved