Totally stuck with some query on very simple table

May be it sounds dumb but I’m trying to build a specific query and I just don’t get it. Here is a sample table:

| Name  | Value1 | Value2 |
|-------|--------|--------|
| Aaa   |    2   |   5    |
|-------|--------|--------|
| Abb   |    4   |   X    |
|-------|--------|--------|
| Acc   |        |   2    |
|-------|--------|--------|
| Add   |    X   |        |
|-------|--------|--------|
| Baa   |        |   3    |
|-------|--------|--------|
| Bbb   |    X   |        |
|-------|--------|--------|
| Bcc   |    4   |   X    |
|-------|--------|--------|
| Bdd   |    1   |        |
|-------|--------|--------|

And here is how I would make the query if MySql was a person:

-Hi, would you please get everything from this table and order it so lines containing “X” in Value1 or Value2 comes first? But keep in mind that Names starting with an “A” must stil be together, same for “B”, etc.

Expected result:

| Name  | Value1 | Value2 |
|-------|--------|--------|
| Abb   |   4    |   X    |
|-------|--------|--------|
| Add   |   X    |        |
|-------|--------|--------|
| Aaa   |   2    |   5    |
|-------|--------|--------|
| Acc   |        |   2    |
|-------|--------|--------|
| Bbb   |   X    |        |
|-------|--------|--------|
| Bcc   |   4    |   X    |
|-------|--------|--------|
| Baa   |        |   3    |
|-------|--------|--------|
| Bdd   |   1    |        |
|-------|--------|--------|

I tried countless combinations of ORDER BY and GROUP BY to no avail.

Please help…

Answer

Something like

SELECT * FROM [table]
ORDER BY LEFT(Name, 1), 'X' in (Value1, Value2) DESC

So it will first order by the first character in the ‘Name’ column (ascending order), and then order based on ‘X’ is in Value1 or Value 2 (descending order, as 'X' in (Value1, Value2) will return 1 or 0.

Hope that makes sense

Advertisement

Answer