CODEWITHSUNDEEP
mysql
Simen Russnes
Simen Russnes

Reputation: 2260

Order final result by column in subquery

I have a query which queries based on the result of a subquery. I want to order by a column in the subquery, but from what I can understand from looking around at similar questions you really shouldn't user ORDER BY in a subquery (which makes this query incorrect).

Is there any way I can do that using aliasing or something along those lines?

In the subquery Table there is a column called "lastUpdated" that I want to order the results from the main query Table by.

Please see my attempted query below:

SELECT * FROM TrackedEntityInstance 
WHERE localId IN 
   (SELECT DISTINCT localTrackedEntityInstanceId FROM Enrollment
    WHERE program IS 'SSLpOM0r1U7' AND orgUnit IS 'gGl6WgM3qzS' 
    ORDER BY lastUpdated ASC);`

Upvotes: 2

Views: 63

Answers (3)

PravinS
PravinS

Reputation: 2584

You can use MySQL FIELD()

Refer: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_field

Firstly you need to take out subquery and create a comma(,) separated string (can use GROUP_CONCAT()) of localTrackedEntityInstanceId in ORDER(ASC) as required

Second you can use the comma separated string in FIELD() function as

SELECT * FROM TrackedEntityInstance ORDER BY field(localId,$string)

hope this works for you.

Upvotes: 0

salih0vicX
salih0vicX

Reputation: 1373

You could use joins as shown below... I think it is faster too..

SELECT *
FROM    trackedentityinstance t1
JOIN
        (
         SELECT DISTINCT localtrackedentityinstanceid, lastupdated
         FROM       enrollment
         WHERE  program IS 'SSLpOM0r1U7'
         AND        orgunit IS 'gGl6WgM3qzS'
         ) t2
ON  t1.id=t2.localtrackedentityinstanceid
ORDER BY t2.lastupdated ASC

I assumed that you have id field in trackedentityinstance table. If not, please adjust the query and give it a try.

Upvotes: 2

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this :

SELECT * FROM TrackedEntityInstance 
WHERE localId IN 
      (SELECT localTrackedEntityInstanceId, MAX(lastUpdated ) AS LastUpdated
       FROM Enrollment
       WHERE program IS 'SSLpOM0r1U7' AND orgUnit IS 'gGl6WgM3qzS' 
       GROUP BY localTrackedEntityInstanceId );`

Upvotes: 0

Related Questions