logo
down
shadow

ORDER BY id IN Subquery


ORDER BY id IN Subquery

By : burdo
Date : November 19 2020, 03:59 PM
may help you . I have a query like this: , Try this query:
code :
SELECT i.* FROM items i LEFT OUTER JOIN cart c 
ON i.id = c.item_id WHERE c.sessID=MY_SESSION_ID AND 
c.item_id is not null ORDER BY c.id 


Share : facebook icon twitter icon
SQL order by sum column, when tie need to set order by subquery

SQL order by sum column, when tie need to set order by subquery


By : Lea
Date : March 29 2020, 07:55 AM
wish helps you I have a SQL Server 2008 query that groups a calculated column "points". When the "points" tie I need to look to another field to determine the correct order. , Something like:
code :
SELECT DriverID, DriverName,CarNum,Points
FROM (SELECT     
           p.DriverID, 
           p.DriverName, 
           p.CarNum, 
           SUM(CASE WHEN r.RaceType = 10 THEN (200 - ((p.CarPosition - 1) * 2)) ELSE 0 END) AS Points,
           MAX(CASE WHEN r.RaceType = 60 THEN p.CarPosition ELSE 999999 END) AS OrderField
      FROM
           RaceParticipants AS p 
           INNER JOIN Race AS r ON p.RaceID = r.RaceID
      WHERE r.RaceDateID IN (255, 256)
      GROUP BY 
           r.RaceDateID, p.DriverID, p.DriverName, p.CarNum
   )sub
ORDER BY 
    Points DESC, OrderField
MySQL does subquery with ORDER BY clause always GROUP BY in the parent query in the same order

MySQL does subquery with ORDER BY clause always GROUP BY in the parent query in the same order


By : user6680931
Date : March 29 2020, 07:55 AM
around this issue No. An ORDER BY in an inline view does not force the GROUP BY operation in the outer query to obtain non-aggregate values from the "first" row in the group. MySQL is free to choose any row from the group.
You may observe that this is what happens, but this behavior is not guaranteed.
Mysql order by subquery id : where a.id = b.id, orders by b.id , query shouldnt order, but it does

Mysql order by subquery id : where a.id = b.id, orders by b.id , query shouldnt order, but it does


By : M. Hunter
Date : March 29 2020, 07:55 AM
Does that help The answer to your first question is simple. SQL result sets are unordered unless you use order by. (Or, in MySQL rely on the deprecated ordering done by group by.) The SQL engine can process the data however it likes. And produce the results however it likes. The resulting order is arbitrary.
Your first query cannot be written more "efficiently", but proper join syntax is highly recommended:
code :
select a.id
from  (select 1 as id union select 2 as id union select 3 as id
      ) a join
      (select 2 as id union select 3 as id union select 1 as id
      ) b  
      on a.id  = b.id;
select a.id
from  (select 1 as id union select 2 as id union select 3 as id) a,  
      (select 2 as id union select 3 as id union select 1 as id) b  
order by  a.id  = b.id desc;
Order result of query with the order of IN subquery

Order result of query with the order of IN subquery


By : jxw123
Date : March 29 2020, 07:55 AM
Hope that helps The reason that it is not ordered by time is because your ORDER BY is in the inner SELECT statement. so it does not apply to the outer SELECT
This should work for you:
code :
SELECT DISTINCT A.* 
FROM   audios AS A INNER JOIN 
       favorites AS F ON A.id = F.audio_id AND F.user_id = ? AND A.status = '1'
ORDER BY F.[time] DESC
Preserve order from subquery (with GROUP BY and ORDER BY)

Preserve order from subquery (with GROUP BY and ORDER BY)


By : Rachmat Febrianto
Date : March 29 2020, 07:55 AM
Hope that helps I am using a smartphone to collect data from the accelerometer and then saving it in a postgresql database, in a server. Basically, each time I read the accelerometer, I save the latitude/longitude at which the smartphone is at the moment, as well as the timestamp where it happened.
code :
SELECT 
latitude, 
longitude, 
count(1) as "Count", 
min(timestamp) as "Start",
max(timestamp) as "End"

FROM table 
GROUP BY latitude, longitude
ORDER BY min(timestamp) asc
Related Posts Related Posts :
  • Postgres Calculate Difference Using Window Functions
  • SQL Server : unsure how to retrieve selected records
  • SQL unique field: concurrency bugs?
  • Query to get the next identity?
  • SQL Server Adding summing values based on the month
  • Join on month if available else join on latest month
  • How to solve error: ORA-01406 fetched column value was truncated?
  • Selecting Field Multiple Times using OR
  • Which SQL datatype can be used to store mobile numbers in numeric forms, without including characters like brackets and
  • Oracle SQL - "Not a group by expression" after adding CASE statements
  • Runnning total of unique string values in column over days
  • VB.Net to AccessDB by SQL - INSERT INTO with parameters error
  • SQL/Excel/VBA - UPDATE query: 'Syntax Error (missing operator) in query expression'
  • Code to combine two sql queries
  • Setting SQL Date field value based off Week Number and Day Number Field
  • SQL query to find matching records but with differences in case sensitivity?
  • How to sort record inside views in SQL Server 2008
  • How to link the output of teradata sql query to an excel sheet?
  • Matching sets in SQL when cross-referencing
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • SQL Server error: Column name or number of supplied values does not match table definition
  • How to find a table name which's column name consist cl_
  • Cannot access existing table from WCF Code
  • SQL CREATE VIEW Error
  • Many Statement in the same transaction PLSQL
  • Tables are not joining properly in sql server 2008
  • DB2 how to sum two column from two different table
  • SQL msAccess: list unfinished courses of a student
  • SQL Server delete multiple rows
  • Finding max(date) from the resultant query output
  • updating multiple columns using case statement in sql server
  • Nested SQL query having select subquery in update statement
  • "putting" a certain object to the screen from inside an array
  • Retrieve segment from value
  • Error while creating a table in oracle 11g
  • Transaction with Sequelize doesn't work
  • Search time complexity of this sql query
  • How to return unique records between two tables without using distinct and union?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org