FAQ Database Discussion Community


how to sort book titles in order without a, an, the using oracle pl sql function

sql,oracle,sorting,plsql,sql-order-by
I am trying to arrange the book title in alphabetical order through oracle pl/sql function but want to avoid 'a', 'an', 'the' in the sorting. I have tried create function using different functions such as LTRIM, TRIM, REPLACE but the the book title are not sorted when I use ORDER...

SQL preserve order

sql,postgresql,sql-order-by
I have an SQL table (PostgreSQL 9.3) with essentially an ID column and a value column (e.g. varchar). I need to be able to reproduce the order in which the rows were entered. The most naive approach would be to either rely on the AUTOINCREMENT feature of the ID column...

SQL: does the order of GROUP BY conditions matter?

mysql,group-by,sql-order-by
My understanding is, the expressions passed to GROUP BY can have any order because the aggregation functions just consider the combination of the columns when accumulating. This is different than ORDER BY when the first column is sorted out before the second and so on. Are the above sentences correct?...

Order by XML attribute value

sql-server,xml,sql-order-by
I have the following table EntityID | Data -----------+----------- 1 | <xml data> 2 | <xml data> 3 | <xml data> Where XML DATA looks like this: <Qualifications> <Qualification QualificationName="Has Dr's degree" Remark="Yes" /> <Qualification QualificationName="ASP.NET Experience" Remark="1 Year" /> <Qualification QualificationName="Sex" Remark="M" /> </Qualifications> I'd like to have the...

How to write queries with concatenation and alias in Oracle?

sql,oracle,oracle11g,sql-order-by,union
I have two tables A and B with following schema :- Table A -> Two columns Principal (varchar) and LoginTime (Timestamp) Table B -> Two columns Username (varchar) and LoginTime (Timestamp) Now I have to run a query on both tables, union them and then order by username. So I...

SELECT DISTINCT user_id of highest value in support_date WHERE

mysql,sql-order-by,max,distinct,where
tb_supporters: user_id support_date ----------------------------- 1 1301283373 2 1301283743 3 1301799207 1 1403862904 2 1405174895 3 1415266390 As the result of the query, it should output the highest support_date of each unique user_id. My actual query: "SELECT DISTINCT(user_id), support_date FROM tb_supporters WHERE support_date < " . (time() - 60*60*24*7) However, it's...

SQL Server — order by clause is dependent on (field-list, type) pair

sql,sql-server,sql-order-by
The query, say "Query-1": select THIS_FIELD, THAT_FIELD, THAT_COUNT from THE_TABLE order by THIS_FIELD is running fine when THIS_FIELD is of type String. however, "Query-2" below: select THIS_FIELD, THAT_FIELD, THAT_COUNT, * from THE_TABLE order by THIS_FIELD isGiving me an Ambiguous column name error to the occurrence of THIS_FIELD in the select...

LINQ equivalent of my SQL Query for UNION ALL

sql-server,linq,sql-order-by,union,equivalent
(SELECT * FROM SheetHay WHERE SheetStatus = 2) UNION ALL(SELECT * FROM SheetHay WHERE SheetStatus = 1) UNION ALL (SELECT * FROM SheetHay WHERE SheetStatus = 0) UNION ALL(SELECT * FROM SheetHay WHERE SheetStatus= 3) I get result set like this: I mean I get all '2' together, '0' together,...

MySql ORDER BY ID , but with exception

php,mysql,sql-order-by
I've an app that load some result from a mysql database. I'm doing this connection with php using this query: $query = mysql_query("SELECT * FROM question ORDER BY id DESC LIMIT ".$intLimit) and this is ok. Now i want the same query but for example i want that the object...

MySQL Order By Case Casting Tinyint to String

mysql,sql-order-by,case
I have the following query: SELECT id, display_order FROM data_element_value WHERE key_id = 32 ORDER BY CASE WHEN (display_order IS NOT NULL) THEN display_order ELSE `value` END The expected output is : id display_order 1 1 13 2 15 3 16 10 But, I'm getting: id display_order 1 1 16...

Codeigniter: Order by ascending date

php,codeigniter,sql-order-by
Hi this is my model code for ordering and getting the data: $this->db->order_by($oBy, "asc"); $query = $this->db->get('books'); Everything is working fine, however in my database i am storing the date as a string, e.g. 01-Jan-2014. Therefore when i order the date it will order it by the day and not...

How do select row with max(column value) group by another column in SQL?

sql-server-2008,tsql,group-by,sql-order-by
i dont't know how to select row with max column value group by another column. I have T-SQL CREATE PROC GET_USER AS BEGIN SELECT A.USER_ID, B.START_DATE , D.START_DATE, A.FULL_NAME,A.COST_CENTER, F.DEPARTMENT_NAME,G.BU_NAME FROM USERS A INNER JOIN USER_PERSON B ON A.USER_ID=B.USER_ID INNER JOIN TYPE_PERSON C ON C.TYPE_PERSON_ID = B.TYPE_PERSON_ID INNER JOIN USER_TRANSACTION...

Mysql - Laravel - order by and rownum position not in sync in 2 queries

php,mysql,sql-order-by,laravel-5,rownum
So I have a list of users, who receive votes, they are ordered by votes DESC, but then I have another query that gets their current position I.E. 1st place, 2nd place, 10th, place, etc. The problem is when people have the same number of votes, the position isn't in...

ORDER BY depending on parameter results in error

sql,sql-server,sql-order-by
I have a stored procedure that initiates an order by depending on a parameter: DROP PROCEDURE [dbo].[GetUsersByClusterAndUserName] GO CREATE PROCEDURE [dbo].[GetUsersByClusterAndUserName] @SortField [nvarchar] (256) = 'UserName', @SortOrder [int] = 0 AS SELECT * FROM [User] ORDER BY CASE WHEN @SortOrder = 0 THEN CASE WHEN @SortField = 'UserName' THEN User_UserName...

MySQL - Retrieve 7 most recent timestamps, sorted oldest first

mysql,sql-order-by
As above, I'm unsure on how to do it, although I am possibly overlooking something simple I want to retrieve the timestamps and another column, just the most recent 7 which I have done using LIMIT and ordered them using ORDER BY timestamp DESC to get the most recent 7......

SQL: Select new columns based on group order

sql,sql-server,group-by,sql-order-by,group
I went through many questions on this site but wasn’t able to find solution. I’ve got a table: Date GroupID CHANNEL 24/02/2015 1 A 26/02/2015 1 B 27/02/2015 1 C 21/03/2015 2 D 20/02/2015 3 E 25/02/2015 3 D 28/02/2015 4 C 04/03/2015 5 B 05/03/2015 5 E 10/03/2015 5...

Create column corresponding to entry rank position in SQL

mysql,sql,sql-order-by
I am using SQL and I have a table that looks like: variable1 123 variable2 39 variable3 993 variable4 2 and I would like to add a column that corresponds to the rank of the integer, had that column been sorted. ie: variable1 123 3 variable2 39 2 variable3 993...

Subquery Order by Oracle

sql,oracle,subquery,sql-order-by
My Database has 3 Tables, Device,Postal_Address and Company. Every Device has an Company_ID, and for every company there is an address. The problem is, the old address is still there if i change it, its everytime making a new entry for this. So if i select all devices with address...

ORDER BY and CASE

sql-server,sql-order-by,case
I have the following script in SQL: ALTER PROC [dbo].[getRequests] @SortBy VARCHAR(50) = 'Date' AS SELECT [ReqDate], [RequestorOrg], [RequestCategory], [ReqDescription], [OrgCountVote] ORDER BY CASE WHEN @SortBy = 'Date (oldest first)' THEN [ReqDate] END ASC, [OrgCountVote] DESC, CASE WHEN @SortBy = 'Date (newest first)' THEN [ReqDate] END DESC, [OrgCountVote] , CASE...

Ordering the results in a controller

c#,asp.net-mvc,sql-order-by
I have the following action in my controller: public ActionResult Details(String id) { MyRecordContext rc = new MyRecordContext(); List<MyRecord> rl = rc.MyRecords.Where(x => x.RecordID == id).ToList(); return View(rl); } How can I add order by clause to my query to order the records on x.RecordName field?...

AngularJS ng-repeat orderby doesn't work properly with greek words

javascript,angularjs,sorting,sql-order-by,ng-repeat
It appears that when I use the orderBy filter inside ng-repeat, in order to sort alphabetically an array of objects by some property, the results I get are wrong. Specifically, orderBy places first all strings starting with letters with accent such as Ά, Έ, Ή, Ί and so on, ignoring...

MySQL Query join last comment to topic

php,mysql,sql,join,sql-order-by
I have a topic and topic_comments table. I want to join them both. +------------+ +------------+ | Topic | | Comments | +------------+ +------------+ | id | | parent_id | | title | | content | | createdate | | createdate | | content | | creator_id | +------------+ +------------+ The...

Does a CASE statement lose alias scope in PostgreSQL?

sql,postgresql,sql-order-by
First of all, this SQL works: select case when s.luserid > 0 then u.szusername when s.lgroupid > 0 then g.szgroup when s.lldapid > 0 then 'LDAP Group' end as name from security s left join users u on s.luserid = u.id left join usergroups g on s.lgroupid = g.id order...

Order by subquery

sql,oracle,subquery,sql-order-by
I have the following oracle SQL code, but I can't understand what is the purpose of ordering by a subquery. Anyone can explain it clearly to me ? SELECT employee_id, last_name FROM employees e ORDER BY ( SELECT department_name FROM departments d WHERE e.department_id = d.department_id ); ...

Advanced Ordering Or Grouping Of Query Results (having?), Access 2013

sql,group-by,sql-order-by,ms-access-2013,having
I REALLY need help. I have a list of faculty members that can either be in assigned to a department or just affiliated with the department. I am trying to query but order the results so that any faculty that appear in the specified department appear in the list first,...

How do I return rows with a specific value last and order by name?

postgresql,sql-order-by
I want my query to return the rows of the table where a column (code) contains a specific value 3 or 4 then move bottom with order by customer If I have a table something like this example: Name Code ------------- Arun 1 Arun 2 Arun 3 Arun 4 Babu...

Algebra Relational sql GROUP BY SORT BY ORDER BY

sql,group-by,sql-order-by,relational-database,algebra
I wanted to know what is the equivalent in GROUP BY, SORT BY and ORDER BY in algebra relational ?

linq query 'orderby' using multiple fields at SQLite plugin by MvvmCross doesn't work

linq,sqlite,xamarin,sql-order-by,mvvmcross
The query is the following: var query = from c in _db.Table<Contact>() where c.FirstName.Contains("Alex") orderby c.Priority, c.FirstName, c.LastName select c; Is it a limitation of SQLite, MvvmCross plugin? Should I go with .Syntax as a result? Error CS1936: An implementation of `ThenBy' query expression pattern for source type `Cirrious.MvvmCross.Community.Plugins.Sqlite.ITableQuery<Contact>' could...

PostgreSQL Order by not working, it doesn’t returns values according to the order

postgresql,count,group-by,sql-order-by
I have two tables players and scores. On the player table I have two fields, name and id (PK) On the scores I have several fields [id_score(PK),winner and looser are foreign key to the id of the player] id tournament and id match. Players id_players | name ------------+----------- 41 |...

PostgreSQL - Slice the bottom N results and move them to the top

sql,postgresql,sql-order-by
Let's say, for example, I have a list of email addresses retrieved like so: SELECT email FROM users ORDER BY email This returns: [email protected] [email protected] [email protected] ... [email protected] [email protected] [email protected] I'd like to take this result set, slice the bottom 3 emails and move them to the top, so you'd...

How to sort data in mysql UNION subquery?

mysql,sql-order-by,union
i need to select all vip from table and order them by rand, and then to add other data sorted by date. In first subquery everything is ok, but in second order by spa_date DESC does not work. I know that ORDER BY clauses inside UNION subqueries are ignored without...

Different ResultSet in MySQL and MariaDB Using GROUP BY and ORDER BY

mysql,group-by,sql-order-by,mariadb
I have a table: voj_submission with following structure: CREATE TABLE IF NOT EXISTS `voj_submissions` ( `submission_id` bigint(20) NOT NULL, `problem_id` bigint(20) NOT NULL, `uid` bigint(20) NOT NULL, `submission_judge_result` varchar(8) DEFAULT NULL, ) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8; And the data in the table may like this: SubmissionID ProblemID UserID JudgeResult 1000...

Datawindow query is not respecting ORDER BY clause

sql,sql-order-by,sybase,powerbuilder,datawindow
I'm developing a form in order to have an order form for procurement purposes. I've a Datawindow as a report object inside my main form. This datawindow (sub-report) has a select to retrieve the orderlines for that order form. Everything is working fine except the ORDER BY clause. I need...

SQL Server Order By - tricky

sql-server,sql-order-by
SortOrder FundType 9 DD 2 DD 13 FF 22 BB 11 FF I want to group by FundType and then Order By SortOrder. But I want to maintain this order w/ respect to FundType: DD, FF, BB So I want the final output to look like: SortOrder FundType 2 DD...

incrementing an ORDER BY statement

mysql,sql,sql-order-by
I have a query that looks like this: SELECT ElapsedTime, COUNT(SurgeryID) as numberOfOps FROM Table WHERE Doctor = 'Dr. ABC' AND numberOfOps > 5 GROUP BY ElapsedTime ORDER BY CASE WHEN ElapsedTime = 'Preoperative' THEN 1 WHEN ElapsedTime = '2 Weeks' THEN 2 WHEN ElapsedTime = '6 Weeks' THEN 3...

Totalling values with the same order name

sql-order-by,echo
I have table in mysql with columns "name" and "views". I want sum views from serial whose name is in "name" column and display list of serials with views by echo from the most popular.

Multiple ORDER BY (get latest element) MySQL

mysql,sql,select,sql-order-by
I have the following tables : Radios, Podcasts and Shows. A radio has many podcasts, and a podcasts has many shows. Each podcast can be ordered by its history, and each show can be ordered by its publication_date. I want to get all the podcasts associated with their latest show....

With multiple related authors, how to display only one when joining on tables

sql,group-by,sql-order-by
I want to return a result set listing all books with a column including the first author (alphabetically for each book). I've got a SQL query that returns all books, joins on author and orders by book author name but this falls over when I then try to group by...

Google App Engine ndb order by length of StringProperty

sql-order-by,app-engine-ndb
Is it possible to order a query by the length of a StringProperty? I tried this: User.query(User.facebook_id = fb_id).order(-len(str(User.name))).fetch(10) but it displays me the error: TypeError: order() expects a Property or query Order;...

MySQL sorting string values with numbers?

mysql,sql-order-by
My data is like this: KRYPTON-4xLV KRYPTON-3xSTG KRYPTON-16xTH KRYPTON-8xTH KRYPTON-8xLV When I call ORDER BY on this column the values are sorted like this: KRYPTON-16xTH KRYPTON-3xSTG KRYPTON-4xLV KRYPTON-8xLV KRYPTON-8xTH Is there a way to sort this like? KRYPTON-4xLV KRYPTON-8xLV KRYPTON-3xSTG KRYPTON-8xTH KRYPTON-16xTH Here is the SQL fiddle: http://sqlfiddle.com/#!9/6d061/2...

Conditional Order by multiple columns Mysql

mysql,sql-order-by,conditional
I have a table consisting of authors, few authors have both Bio and Image, few have only image, few have only bio, and few have neither.. bio and image are stored in different columns, I want to sort my query result (order by ) in such a way that authors...

How do I show a range of numerical values first in SQL?

sql,sql-server,sql-order-by
How would I arrange this so that it is still ordered by time but also any IDs under 10 would be shown first? Time | ID 9:15 | 1 9:20 | 5 9:25 | 12 9:30 | 3 9:35 | 26 9:40 | 22 9:45 | 15 Can this be...

MySQL query with less than and ORDER BY DESC

mysql,indexing,sql-order-by,where,where-clause
I'm doing some work with a rather large set of data and am trying to create a query from every combination of four different pieces of data. All of those pieces combined form a staggering 122,000,000 rows. Then, I'm trying to find a weight that is less than a certain...

MySQL: Sort items by category count

mysql,sql-order-by
I currently use the following query which works perfectly: SELECT * FROM `items` WHERE `id` IN (SELECT `item_id` FROM `categories_items` WHERE `category_id` IN (1, 2) GROUP BY `item_id` HAVING COUNT(`item_id`) = 2); It selects all the items that are in all the selected (checkboxes) categories. The problem is that most...

Efficient way to get N rows order by complex expression in MySQL

mysql,performance,sorting,sql-order-by,limit
I have a table line_item { id: int, price: decimal, quantity: int, [other:...] }. This table is very huge, approx. 28 million rows. Now I want to get top 1000 rows order by f(price, quantity, [other...]), f is a arbitrary function. What is the best way to do that? I...

Order by conditional count of associated records

sql,postgresql,sql-order-by,left-join,aggregate-functions
One quiz can have none, one or many submissions. Each submission has submissions.correct = t or submissions.correct = f. What's the best Postgres 9.4 query to get all quizzes (regardless of whether they have a Submission or not), ordered by the number of associated submissions with correct = t in...

mySQL ordering and distinct

php,mysql,group-by,sql-order-by
I have a review system on my website where users (profiles) can leave reviews for others. This part works fine. But on my home page i want to get 10 of the latest reviews BUT i don't want to duplicate any users/profiles (i.e if a user has 2 reviews i...

select value among other values of a different tha column used for Group by in MYSQL

mysql,sql,group-by,sql-order-by
so i have three columns like company id project id approved status 1 3 NOT 2 4 APPROVED 3 5 null 2 6 NOT 3 7 APPROVED 3 8 null 2 9 NOT 2 10 APPROVED 3 11 null i have achieved this using left join now what i want...

Strange behaviour of SQL order by when the string consists space

sql-server,string,sql-order-by,space
SQL Example 1: SELECT TestField FROM (VALUES('Ne'), ('NE')) AS TestTable(TestField) ORDER BY TestField COLLATE Latin1_General_CS_AS Result 1: Ne NE SQL Example 2 (between NE and a there are 2 spaces, while between Ne and a there is only 1): SELECT TestField FROM (VALUES('Ne a'), ('NE a')) AS TestTable(TestField) ORDER BY...

ORACLE order by MONTH

sql,oracle,sql-order-by
How can I order by month in full name of the month? And I want to get the results like this below: FEBRUARY 0 80 MARCH 0 58 APRIL 0 39 This is my 1st simple script: select to_char(month,'MONTH')month,mo_incoming,mt_outgoing from t_raw_settlement_tara_yearly order by month output: APRIL 0 39 FEBRUARY 0...

Is there a way to use ORDER BY and still use deleterow()?

sql,sql-order-by,derby
The code I'm using deletes the last row in the database. From what I have read, once I've used ORDER BY it will set the result back to read only which makes deleterow() or anything else involving updating the database not possible. Is there a work around so I could...

MySQL Order By timestamp using IN clause

mysql,mysqli,order,sql-order-by,sql-in
I have the below query and it works fine at returning results based on the set timestamp. SELECT * FROM catalog WHERE part IN (SELECT part FROM query_data WHERE timestamp >= '2015-02-02') LIMIT 10 What I would like to do is get the results from the above but ORDER BY...

Use Union All & Order By In View?

sql-server,view,sql-order-by,union-all
I need to order by a date column contained in both of my tables in my view. Below is the syntax I use for my view, but if I try to use the order by statement in the view I get this error: Msg 1033, Level 15, State 1, Procedure...

How to use OFFSET and Fetch without Order by in SQL Server

sql-server,sql-server-2012,sql-order-by,fetch,offset
I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have...

How to arrange columns based on a particular order of values present in the rows

sql,oracle,oracle11g,oracle10g,sql-order-by
Example: Table Structure: EMP_ID SAL_MONTHWISE(JAN) SAL_MONTHWISE(FEB) SAL_MONTHWISE(MAR) 1 10000 15000 8000 2 20000 2000 10000 3 50000 60000 40000 New Format Required: In the Increasing order of the salary for each employee EMP_ID SAL_MONTH1 SAL_MONTH2 SAL_MONTH3 1 8000 10000 15000 2 2000 10000 20000 3 40000 50000 60000 Thanks In...

How do I return rows with a specific value first and order by a different column?

sql,sql-order-by
I need to write a query that will pull a record with a certain ID first then order the rest of the list by a different column This is what I have done so far but it is not ordering it by score, only ID with the id I need...

Nginx: deny 'orderby=rand' wordpress url

sql,wordpress,nginx,sql-order-by
I want to block url 'http://example.com/?orderby=rand' on my websites by nginx. I thought it'll be easy (like i've blocked 'wp-admin' section). But this code doesn't work, and i don't know why: location ~* \orderby=rand { deny all; } This is very slow and consumes a resource feature of Wordpress. ...

mysql: order -> limit -> sum… possible?

mysql,group-by,sum,sql-order-by,sql-limit
i am loosing it over the following problem: i have a table with participants and points. each participant can have up to 11 point entries of which i only want the sum of the top 6. in this example lets say we want the top 2 of 3 +----+---------------+--------+ |...

Order by number of times a tuple appears- msql

mysql,group-by,sql-order-by
Example of table Name FriendName A C, A D, B Z, E B, E F, E G, I want to display the table such as that the name with the most friends should show first with all its respective tuples. Like the output I want is: E B, E F,...

Sorting table fetch using 2 order by clase in mysql

php,mysql,sorting,sql-order-by
I want to sort data fetched from the database by the number of votes My MySql code ends with ORDER BY number_of_votes DESC. But if there's a tie in the result, I want to break it by favoring the entry that was submitted first, so how can I do this...

sort a table while keeping the hierarchy of rows

sql,oracle,sql-order-by
I have a table which represents the hierarchy of departments: +-----------+--------------+--------------+--------------+-----------+-------+ | Top Dept. | 2-tier Dept. | 3-tire Dept. | 4-tier Dept. | name | tier | +-----------+--------------+--------------+--------------+-----------+-------+ | 00 | | | | abc | 0 | | | 00-01 | | | bcd | 1 | |...

Dealing with Order by CASE Statements and NULL value

sql-server,sorting,sql-order-by,case
I'd like to custom order by based on a column. If the column value is NULL or '' (empty string), then I want to display that row first. SQL Server does not like the following: ORDER BY CASE WHEN ISNULL(SendDate.ValidTo, '') = '' THEN NULL END or ORDER BY CASE...

Use DISTINCT with ORDER BY clause

sql-server,select,sql-order-by,distinct
I want to Alter my following stored procedure ALTER Procedure [dbo].[spEGCRedemptionReportForMHR] @DateTo datetime, @DateFrom datetime, @MerchantID varchar(11), @Pan varchar(16) As Set @DateTo = @DateTo +1 Select Distinct Convert(varchar(50),pt.TransactionDate,103) 'TransactionDate', m.MerchantName1 MerchantName, m.MerchantAddress Location, m.MerchantID, pt.TerminalID, pt.batchnumber 'Batch #', pt.SequenceNumber 'Receipt #', pt.PAN 'Card Number', c.EmbossName 'Card Holder Name', Convert(Decimal(10,2),Case when...

How does postgres order the results when ORDER BY is not provided

sql,postgresql,select,sql-order-by
Let's say I have two tables: user (user_name varchar(50), project_name varchar(50)) project (project_name varchar(50), project_cost(integer)) I have a query that's returns me results which are "de-facto desired" : select u.user_name, p.project_name from user u, project p where u.project_name = p.project_name Postgres says that order of rows is not predictable when...

Ordering rows when using IN on Microsoft SQL Server

sql,sql-server,sql-order-by
I'm trying to get some SQL result from a list of ids. I have my ids ordered just as I need them, but when I pull the result from the query, the ids are not ordered as I need them. I need the idParada ordered just as I sent them...

SQL Assign “co-efficients” to query conditions and use them to sort result

mysql,sql,sql-order-by
I saw a query once that assigned some kind of ranking to query conditions, I can't remember it now. They way I understood it, i think variable names (s1,s2,...) were assigned to each of the conditions with a coefficient to give them different "weights" then the sum of the variables...

SQL Sorting table based on two interchangeable fields

sql,postgresql,sorting,sql-order-by
I want to sort a table having 3 columns (time, source , recipient) by the order by which communication is being made. If the source and recipient are conversing together then it will list them by the time. The goal is to see the communication happening between similar people ordered...

sql server - inserting rank column in SP table-parameter

sql,sql-server,sql-order-by
My problem is similar to this one: how to maintain order while doing join in sql Basically I have a table parameter in a stored procedure that contains some IDs in a particular order. I want to use those IDs in an inner join and return results based on the...

Order by pairs of values

sql,sql-server,sql-server-2008,sql-order-by
I have a set of rankings, ordered by group and ranking: Group | Rank ------------ A | 1 A | 2 A | 3 A | 4 A | 5 A | 6 B | 1 B | 2 B | 3 B | 4 C | 1 C |...

Conditional ordering with priority to a column

mysql,sql,sql-order-by
I have a table with 4 columns: ProductID IsSponsored Rank PopularityScore I want to get top 20 Products ordered by Rank and PopularityScore. However Rank is considered only if IsSponsored = 1. I have tried using CASE in ORDER BY clause. Something like: SELECT ProductID FROM ProductTable ORDER BY CASE...

Specific Ordering in SQL

sql,sql-server,sql-order-by
I have a SQL Server 2008 database. In this database, I have a result set that looks like the following: ID Name Department LastOrderDate -- ---- ---------- ------------- 1 Golf Balls Sports 01/01/2015 2 Compact Disc Electronics 02/01/2015 3 Tires Automotive 01/15/2015 4 T-Shirt Clothing 01/10/2015 5 DVD Electronics 01/07/2015...

SELECT columns different from GROUP BY columns

mysql,oracle,group-by,sql-order-by,aggregate-functions
Having this database schema (just for illustration purpose) [articles (id_article, title)] [articles_tags (id_tag, id_article)] [tags (id_tag, name)] using MySQL it's possible to do: SELECT a.title, COUNT(at.id_tag) tag_count FROM articles a JOIN articles_tags at ON a.id_article = at.id_article JOIN tags t ON t.id_tag = at.id_tag GROUP BY a.id_article ORDER BY tag_count...

ORDER BY alternative values from main- and subquery

sql,postgresql,sql-order-by,postgresql-9.3
Right now I have a query that looks something like so: SELECT id FROM post_table post WHERE post.post_user_id = user_id ORDER BY (SELECT max(comment_created_date) FROM comments_table WHERE comments_post_id = post.id) DESC, post.post_created_date DESC My idea for this query was that it would order a series of posts like so Post...

ORDER BY UPPER(column) is wrong in MySQL

mysql,sql,order,sql-order-by,mysql-5.6
With MySQL 5.6, given the following table structure: mysql> describe groups; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(500) | YES | | NULL | |...

mysql Quiz leaderboard filter by points, time taken

mysql,sql,select,sql-order-by,grouping
I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken. here is a link to a sql fiddle http://sqlfiddle.com/#!2/65fbf0/1 I am really...

Sorting rows with 2 episodes number in mysql

mysql,order,sql-order-by,rows
I have a table that is something like this, but the problem is, whenever I try to set them in order based on their episode number from lowest to highest I get different results. id | episode 1 (Title) Episode 1 2 (Title) Episode 4 3 (Title) Episode 2 4...

Ordering by average rating - how to speed it up?

sql,postgresql,indexing,sql-order-by,postgresql-9.3
I have two columns in a table: rating_total integer rating_count integer rating_total is a sum of total rates, and rating count is a number of total rating. I want to order by average rate: ORDER BY (rating_total/rating_count) DESC, id ASC Should I create separate column for average_rate? Or it is...

Group_concat() not sorting according to tha data

mysql,sql,sql-order-by,dynamic-sql
Sorted data is what I want and I am not getting it from this order by clause addition to this statement. SELECT GROUP_CONCAT(DISTINCT CONCAT( 'COUNT((CASE WHEN (`ltd`.`total_pass_pc` =',total_pass_pc,') THEN 1 ELSE NULL END)) AS `TC=',total_pass_pc,'`' ) )INTO @sql FROM log_tc_data ORDER BY `total_pass_pc`; the results that this group_concat produce for...

Sorting NULL values in PostgreSQL [duplicate]

sql,postgresql,sorting,null,sql-order-by
This question already has an answer here: PostgreSQL sort by datetime asc, null first? 2 answers In PostgreSQL, I'm using ORDER BY on columns of various types (NUMERIC, TEXT, BOOLEAN, TEXT[]). I'm designing an AJAX GUI where the users may interactively sort records by individual columns (in both ascending...

Join two sql queries with ORDER BY

php,mysql,sql,sql-order-by
SELECT item_name, alt_names FROM antidepressants WHERE status = 'verified' ORDER BY priority, item_name I'm using the above query within PHP to order the columns based on a priority, priority is set 1-6 and the rest of the records are set to priority 1000. This is an example of the table...

orderby () containing numbers and letters

c#,wpf,linq,linq-to-sql,sql-order-by
I want to order an array containing numbers and letters in an ascending order the values are stored as a string in a DB. This is the list I have 4B 1A 1 1B 2 4C 4 and want them to order like this where numbers are ascending followed by...

How to order MySQL data in table with PHP with intval $GET value [duplicate]

php,mysql,get,sql-order-by
This question already has an answer here: mysql_fetch_array() expects parameter 1 to be resource (or mysqli_result), boolean given 29 answers I'm making a sortable table with MySQL data. I want customers.php?order_by=lastname to order the table by the lastname column. I tried with these two lines: $order_by = intval($_GET['order_by']); $customers...

ORDER BY clause is not sorting by alias in SQL Server

sql,sql-server,tsql,sorting,sql-order-by
I have a query like this: DECLARE @Sortorder VARCHAR(5) = 'asc', @ColumnNumber INT = 9 SELECT SUBSTRING(csu.UserName, CHARINDEX(CHAR(92), csu.UserName) + 1, LEN(csu.UserName)) AS UserName, w.WorkItemId FROM [tasks].[WorkItems] w LEFT JOIN operations.CustomerServiceUser csu ON csu.UserId = w.AssignedToUserId WHERE w.[ShowInTaskList] = 1 AND UserName IS NOT NULL ORDER BY CASE WHEN @ColumnNumber...

AtTask API order by date?

api,order,sql-order-by,attask
I've gone around in circles on the web trying to find any relevant information, including the API Docs, for ordering results by ASC/DESC order... /task/search?name=NAME HERE&percentComplete=0&fields=plannedCompletionDate,project:status,project:name,project:plannedCompletionDate I want to be able to have my results ordered by plannedCompletionDate instead of having to do it in code. ...

OrderByDescending by count of items via Lambda expressions

c#,linq,lambda,sql-order-by
I have type MyClass : MyClass { public string Name { get;set; } public string LastName { get;set; } } and also i got my collection: List<MyClass> _Lst = new List<MyClass>(); where i add some items like this: UPD : _Lst.Add(new MyClass{Name = "Petya" , LastName = "sergeevi4"}; _Lst.Add(new MyClass{Name...

sorting via order by and row_number via user variable in mariadb

mysql,variables,sql-order-by,mariadb,temp-tables
I've read that any order by Operation is done at the end of the query and therefore sorting cannot be forced in a result before. So what I'm trying to achive is to get only one record from a result with duplicates by using variables which assign row numbers and...

Avoid filesort with INNER JOIN + ORDER BY

mysql,sql,join,sql-order-by,filesort
I've been reading other posts but I didn't managed to fix my query. Using DESC order the query is x20 times slower, I must improve that. This is the query: SELECT posts.post_id, posts.post_b_id, posts.post_title, posts.post_cont, posts.thumb, posts.post_user, boards.board_title_l, boards.board_title FROM posts INNER JOIN follow ON posts.post_b_id = follow.board_id INNER JOIN...

SQL & SQL Server - Denormalized table, Distinct and Order By Complex Query

sql,sql-server,select,sql-order-by,distinct
I've been working lately in this and I couldn't work it out. I have the following table: ID Language Text ---- -------- -------- 1 spanish Hola 1 english Hello 2 spanish Chau 2 english Goodbye 2 french Au revoir 3 english Thank you I need to get each ID once...

Mysql Paging With multiple ORDER BY

mysql,sql,sql-order-by,offset,sql-limit
I have some issue with mysql pager. Count all rows is 164. pro_type - can be in 3 different integer values; pro - integer value; cr_date - some date. Here is my queries: SELECT id FROM table WHERE f1='1' AND f2='0' AND uid=43 ORDER BY pro_type DESC, pro DESC, cr_date...

Select the latest 3 records for each ID in a table

sql,sqlite,group-by,sql-order-by,greatest-n-per-group
I have a table with a composite primary key (ID, Date) like below. +------+------------+-------+ | ID | Date | Value | +------+------------+-------+ | 1 | 1433419200 | 15 | | 1 | 1433332800 | 23 | | 1 | 1433246400 | 41 | | 1 | 1433160000 | 55 |...

Why does JPA “order by ascending” returns upper case entries first?

java,jpa,sql-order-by
I have a criteria query with the following code: CriteriaQuery<MyClass> criteria = cb.createQuery(MyClass.class); Root<MyClass> root = criteria.from(MyClass.class); criteria = criteria .where(cb.like(root.get(MyClass_.name), name + "%")) .orderBy(cb.asc(root.get(MyClass_.name))) .select(root); Why is my example result ordered this way: abcdE abcde and not like this: abcde abcdE and how can I fix that? I would...

Mysql - update table column from another column based on order

mysql,sql-order-by
I have a table with columns id, name, position, old_position. The column old_position contains unique numbers that are used to define the order when making select. But the numbers are not consecutive(even if when ordered). E.g. 2, 12, 11, 14, 20, 35, 45, 28, What I am trying to achieve...