Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

MYSQL/PHP: Individually sorting united SELECT statements

Hello,

I'm trying to individually sort united SELECT statements.

My original query is attached.

Which lines would I modify to sort each SELECT by name1.col7 (which is the users last login time)?

For a simplified example of this, let's say the user is from the US (name1.col6 = 'US') and their results without sorting produced:

RESULT A
Username - Country - Last Login
USER1 US 3 min ago
USER7 US 1 min ago
USER4 US 7 min ago
USER8 CA 8 min ago
USER9 ZA 5 min ago
USER2 RU 1 min ago

I would like that Result A - after sorting by name1.col7 (last login) to be:

RESULT B (sorted)
Username - Country - Last Login
USER7 US 1 min ago
USER1 US 3 min ago
USER4 US 7 min ago
USER2 RU 1 min ago
USER9 ZA 5 min ago
USER8 CA 8 min ago

Notice how each SELECT is now ordered by name1.col7 (last login) while still maintaining each SELECT separate (the "US" are together, then the rest).

Right now my Original SELECT works to do RESULT A perfectly, and now I just want to do the ORDER BY to get RESULT B (sorted, each group separately).

So which lines would I modify and how to achieve RESULT B considering that right now my query gives me RESULT A?

I tried adding parenthesis and an individual ORDER BY to each SELECT, but that failed.  I tried adding another column "sort_column" to sort by that then by name1.col7, but it produced duplicate results in the second SELECT.

Anyway, I hope that's clear enough.  Thanks for your help.
SELECT name1 . * , name2 . * 
FROM table1 AS name1 
LEFT JOIN table2 AS name2 
USING ( 
col1 
) 
LEFT JOIN table3 AS name3 
USING ( 
col1 
) 
WHERE name1.col1 != 'A' 
AND name3.col2 = 'B' 
AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 ) 
AND name1.col4 LIKE 'C' 
AND name1.col5 LIKE 'D'
AND name1.col6 = 'E' 
GROUP BY name1.col1 
UNION ( 
SELECT name1 . * , name2 . * 
FROM table1 AS name1 
LEFT JOIN table2 AS name2 
USING ( 
col1 
) 
LEFT JOIN table3 AS name3 
USING ( 
col1 
) 
WHERE name1.col1 != 'A' 
AND name3.col2 = 'B' 
AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 ) 
AND name1.col4 LIKE 'C' 
AND name1.col5 LIKE 'D' 
GROUP BY name1.col1 
)

Open in new window

0
NeedExpertHelp
Asked:
NeedExpertHelp
  • 2
1 Solution
 
NeedExpertHelpAuthor Commented:
I just found this on the MYSQL site which is the solution I'm looking for:

------------------------------------------
Posted by Phil McCarley on February 28 2006 6:37am

In addition to the above comment regarding the ORDERing of individual SELECTS, I was after a way to do exactly what is says wouldn't work. I have two playlists, and to get the correct order I need to use two different ORDER clauses, also I wanted to use the DISTINCT functionality of the UNION syntax.

What I needed was the contents of each playlist to be ordered in there specific way, while the first appeared wholly before the second. Also, I couldn't use the various tricks of adding extra colums to sort on because that left me with non-unique rows and therefore if the same entry was in both lists, the duplicate didn't get removed.

How I overcame this was to use subqueries, as follows:

SELECT song_id FROM
(SELECT song_id FROM play_immediate
ORDER BY play_id DESC) AS t1

UNION

SELECT song_id FROM
(SELECT song_id FROM play_later
ORDER BY play_id) AS t2

And using this I am able to sort each list differently, one ascending and one descending, keep the 'immediate' list before the 'later' list but still remove all duplicates.

Hope this helps others.
------------------------------------------

How can I implement the above into my query to get it to work?

Thanks again!
0
 
NeedExpertHelpAuthor Commented:
?
0
 
fiboCommented:
I would àprobably avoid the UNION which might create performance problem, and would instead usd an extra variable to find if this a US login.

So a first attemp at code would be
SELECT name1 . * , name2 . *, (country='US') as US
FROM table1 AS name1,
    table2 AS name2,
    table3 AS name3  
WHERE
name1.col1=name2.col1
AND name1.col1=name3.col1
AND name1.col1 != 'A'  
AND name3.col2 = 'B'  
AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 )  
AND name1.col4 LIKE 'C'  
AND name1.col5 LIKE 'D'
AND name1.col6 = 'E'  
GROUP BY US DESC, name.col3 ASC, name1.col1  

where you will need to adapt the country field name.
As it is currently written, this request has several problems:
- the test for col6 in your initial query is not clear, and this test should probably be removed.
- tests such as LIKE 'C' should be replaced with = 'C'
- I presume your 3 tables are indexed on col1...
- in most situations, SELECT * should be replaced by the fields list
SELECT name1 . * , name2 . *, (country='US') as US 
FROM table1 AS name1,
    table2 AS name2,
    table3 AS name3  
WHERE
name1.col1=name2.col1
AND name1.col1=name3.col1 
AND name1.col1 != 'A'  
AND name3.col2 = 'B'  
AND name3.col3 > ( UNIX_TIMESTAMP( ) -7200 )  
AND name1.col4 = 'C'  
AND name1.col5 = 'D' 
GROUP BY US DESC, name.col3 ASC, name1.col1

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now