Sql: Sort by Priority, But Put 0 Last

SQL: Sort by priority, but put 0 last

Try:

order by case priority when 0 then 2 else 1 end, priority

Sort numbers with 0 as the last priority, PL/SQL keyword for priority

Try:

ORDER BY CASE balance 
            WHEN 0 THEN null
            ELSE balance
         END 
      DESC NULLS LAST

sql server order by date and priority

You need to cast your order by LastModifiedDate as a date to remove the time element from your ordering.

Updated to handle the case where you have multiple records with the same priority on the same day, and want those ordered by the time.

SELECT 
  [Id]
  ,[Priority] 
  ,[LastModifiedDate]

  FROM [News] order by CAST(LastModifiedDate AS DATE) desc, Priority desc, LastModifiedDate

Priority in ORDER BY clause

The columns or expressions in an order by specify the ordering of the rows in the result set.

If you have multiple columns or expressions (called keys), the first key is used to sort the data. The second key is only used when there is a tie for the first key. Similarly, the third key is only used when there is a tie for the first two keys.

So, the result is a lot like a telephone book order by (lastname, firstname). The firstname is only used when there are multiple entries for the same lastname.

SQL - sorting data according to the first priority when using LIKE in Clause

Your queries are not going to be as fast as they could because:

1. You use the lower function when LIKE is already case insensitive. select 'a' LIKE 'A' will return true
2. Using a column as the argument of a function in your WHERE forces the server to bypass its index and process the row: eg: lower(i.name). This should be avoided in general

If you have MySQL 5.6+, and if store_items.name is of type CHAR, VARCHAR or TEXT, then add a FULLTEXT index to your name column to optimize it for text searches.

ALTER TABLE store_items ADD FULLTEXT fulltext_name (name);

Then modify your query to use the MATCH...AGAINST syntax:

SELECT i.name,i.add_time,round(i.price),s.store_address
FROM store_items i JOIN stores s ON i.store_id = s.store_id
WHERE MATCH(i.name) AGAINST('samsung galaxy')

This search will return all rows that have at least one of the two words samsung and galaxy. Rows that have both words will be higher in the result set.

From the docs:

MATCH() takes a comma-separated list that names the columns to be
searched. AGAINST takes a string to search for, and an optional
modifier that indicates what type of search to perform

...

Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6
and up, they can also be used with InnoDB tables.) Full-text indexes
can be created only for CHAR, VARCHAR, or TEXT columns

Ordering by specific field value first

There's also the MySQL FIELD function.

If you want complete sorting for all possible values:

SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core", "board", "other")

If you only care that "core" is first and the other values don't matter:

SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core") DESC

If you want to sort by "core" first, and the other fields in normal sort order:

SELECT id, name, priority
FROM mytable
ORDER BY FIELD(name, "core") DESC, priority

There are some caveats here, though:

First, I'm pretty sure this is mysql-only functionality - the question is tagged mysql, but you never know.

Second, pay attention to how FIELD() works: it returns the one-based index of the value - in the case of FIELD(priority, "core"), it'll return 1 if "core" is the value. If the value of the field is not in the list, it returns zero. This is why DESC is necessary unless you specify all possible values.

How to give priority to a single employee while sorting the employee name in sql?

Simply use the correct ORDER BY clause:

Sample data:

SELECT *
INTO Employee
FROM (VALUES
   ('Girish', 'BB', 20000),
   ('Bhanu',  'AA', 10000),
   ('Mahesh', 'CC', 10000),
   ('Seema',  'YY', 30000)
) Employee (Emp_name, Dept_name, Salary)

Statement:

SELECT *
FROM Employee
ORDER BY 
   CASE
      WHEN Emp_name LIKE '%Mahesh%' THEN 1
      ELSE 2
   END,
   Emp_name ASC

Sort data ASC priority to data not = 0

You need to use conditional order by. Here is the SQL fiddle

Order by with underscores as highest sorting priority

The short answer is that you probably want to use the REPLACE() function, and swap out those _'s for a character that's sorted before letters.

However, there's a bit of a complication: how large is your data, and how integral is the sorting to your code? The reason I'm asking is because doing this sort of thing kinda makes any indexes on that column useless (because the index's ordering isn't what you're wanting.)

Take a look at 'Generated Columns' (https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html) - you may need to add a 'generated' column to your table, and then create an index on it.

---

Related Topics