1

I have a table, employee, and one of the columns is last_name, which stores strings.

Using a SELECT query, I am trying to get the last_name column, ordered alphabetically, as one column, and another column, again last_name, this time ordered in reverse alphabetical order.

The desired result looks something like

+-----------------+----------------+
| last name       | last name      |
+-----------------+----------------+
|  Apple          | Xylophone      |
+-----------------+----------------+

I have tried

SELECT a.last_name, b.last_name
FROM employee A
LEFT JOIN employee B
ON a.last_name = b.last_name
ORDER BY a.last_name, b.last_name DESC;

Which, of course, doesn't work, because it orders both columns by last_name in alphabetical order, and doesn't even get to the other order condition because the syntax specifies that condition n is only used if some rows are equal under condition n-1.

In my head, I see this as something like

SELECT last_name 
FROM employee 
ORDER BY last_name;

and

SELECT last_name 
FROM employee 
ORDER BY last_name DESC;

But I don't know how to combine the two. When I UNION these two queries, I get the right data, but it's in the form of two columns concatenated into one, when I need both columns kept distinct.

I tried using a JOIN, here, which was a total shot in the dark as I've never seen syntax like this:

(SELECT a.last_name 
FROM employee a 
ORDER BY a.last_name 
LIMIT 1) 
LEFT JOIN 
(SELECT b.last_name 
FROM employee b 
ORDER BY b.last_name DESC 
LIMIT 1) 
ON a.last_name = b.last_name;

and of course that didn't work either. So I have no clue on how to proceed.

3
  • Can you provide a sample of the data you are using? Have you tried SELECT MIN(Last_Name), MAX(Last_Name) FROM employee?
    – user212533
    Commented Oct 25, 2022 at 20:23
  • I don't fully understand what you're trying to do. Are you trying to merge all last_names? Please share a sample data, you can use something like this: dbfiddle.uk/uUrg--Ep Commented Oct 25, 2022 at 20:24
  • @bbaird That is actually exactly what I needed. Thank you
    – m. lekk
    Commented Oct 25, 2022 at 20:26

1 Answer 1

6

One way to solve this is using ROW_NUMBER() function, available in MySQL version 8.0. We can get row numbers with the different orders in two subqueries (derived tables or CTEs) and then join them.

Tested in dbfiddle.uk:

WITH 
  a AS
  ( SELECT 
        last_name,
        ROW_NUMBER() OVER (ORDER BY last_name) AS rn
    FROM employee 
  ),
  d AS
  ( SELECT 
        last_name,
        ROW_NUMBER() OVER (ORDER BY last_name DESC) AS rn
    FROM employee 
)
SELECT
    a.last_name AS last_name_a,
    d.last_name AS last_name_d
FROM a JOIN d USING (rn) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.