CODEWITHSUNDEEP
sqltimesql-order-by
Manish Arora
Manish Arora

Reputation: 271

SQL query order by time asc doesn't work

SELECT time,
       null as postid,
       designid,
       custid 
FROM approveddesign 
WHERE designerid='[email protected]' 
UNION 
SELECT time,
       postid,
       null as designid,
       null as custid 
FROM applycontest
WHERE approval='approved' and designerid='[email protected]' 
ORDER BY 'time' ASC

Sort by time is not working as time column has numeric values only.

Upvotes: 0

Views: 572

Answers (3)

shA.t
shA.t

Reputation: 16968

A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list.

If you really want to order all the result use this:

SELECT *
FROM (
    SELECT time,
           null as postid,
           designid,
           custid 
    FROM approveddesign 
    WHERE designerid='[email protected]' 
    UNION ALL 
    SELECT time,
           postid,
           null as designid,
           null as custid 
    FROM applycontest
    WHERE approval='approved' AND designerid='[email protected]' 
    ) t
ORDER BY 1

I also change UNION to UNION ALL for a better performance, but it will not remove duplicate rows, If you want to remove those duplicate rows too, I suggest you to add DISTINCT after SELECT like this:

SELECT DISTINCT *
FROM (
    ...
    ) t
ORDER BY time

Upvotes: 0

pavel
pavel

Reputation: 27072

Remove quotes around the col name in ORDER BY clause.

[...] ORDER BY time ASC

Upvotes: 1

mbaula
mbaula

Reputation: 37

Change 'time' to time to be ORDER BY time ASC

Upvotes: 1

Related Questions