homepage Welcome to WebmasterWorld Guest from 54.226.168.96
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe and Support WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




msg:4314719
 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?

 

rocknbil




msg:4314795
 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.

dataguy




msg:4314971
 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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved