Sorting by multiple, weighted columns

Forum Moderators: open

Message Too Old, No Replies

Sorting by multiple, weighted columns

instead of by one column after the other

dataguy

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

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

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.