1

I want in the output a hierarchical order like so:

My Data :

Name            | Cost   | Level 
----------------+--------+------
Car1            | 2000   |   1
 Component1.1   | 3000   |   2
  Component1.2  | 2300   |   3
Computer2       | 5000   |   1
 Component2.1   | 2000   |   2
  Component2.2  | Null   |   3

Output: Show all those data, which has money in it and order it by the level, something like first 1, then 2, then,3 and after that start with 1 again.

Name              | Level
------------------+------
Car1              |   1
 Component1.1     |   2
  Component1.2    |   3
Computer          |   1
 Component2.1     |   2

What ORDER BY does is:

Name            | Level
----------------+------
Car1            |   1
Computer1       |   1
Component1.1    |   2
Component2.1    |   2
Component1.2    |   3
Component2.2    |   3

I tried the CONNECT BY PRIOR function and it didn't work well

 SELECT Name, Level
   FROM Product
CONNECT BY PRIOR Level;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Mundo.Corjellito
  • 11
  • 2
  • 2
    So what is the input data that should generate that output? And what have you tried? **[edit]** your question by clicking on the [edit] link below it. Do not post code in comments –  Oct 20 '18 at 10:31
  • 3
    The question is incomplete. You have shown us only what you expect to see after running the query and the missing piece here is how your original table looks like. It is not possible to help you if you are not willing to show it and also tell us on what basis the resulting order has to be assigned. – Kaushik Nayak Oct 20 '18 at 11:16
  • I am willing to show my data, but I'm not really sure how clear I have to describe it. – Mundo.Corjellito Oct 20 '18 at 11:53

4 Answers4

0

In MySQL you would normaly use 'order by'. So if you want to order on table row "level" your synntax would be something like this:

SELECT * FROM items ORDER BY level ASC

You can make use of ASC (Ascending) or DESC (descending).

Hope this will help you.

JesseH
  • 53
  • 8
  • 1
    I really believe that should be it... Could you post some more of your code? – JesseH Oct 20 '18 at 10:40
  • No what it does is order from 1 to 3 asc, but what i want is something like the output that i have given – Mundo.Corjellito Oct 20 '18 at 10:58
  • 1
    You want to order the Name? Than you simply have to change the order by like below. `SELECT Name, Level FROM Auction ORDER BY Name ASC` – JesseH Oct 20 '18 at 11:16
0

You can use below one

SELECT Name, Level
FROM Auction
ORDER BY Name, Level
WHERE Money!='Null' ;

Doing order by Name will print the result in hierarchical order, but if it has a column called parent id, then it would have been easier to show.

Ranjit Singh
  • 3,715
  • 1
  • 21
  • 35
0

i suggest this for you :

SELECT Name, Level FROM Product ORDER BY Name, Level WHERE Money!='Null' ASC;

i wish this help you brother

MBARK T3STO
  • 329
  • 1
  • 11
0

It is still not clear whether this is what you are really expecting. It seems to me from your data set, you want to numerically order the components based on some kind of a version number at the end of the component. If that's truly what you want then you may ignore the non-numeric characters in the name and order by pure numbers towards the end of string ( with the required where clause ).

ORDER BY REPLACE ( name, TRANSLATE(name,' .0123456789',' '),'');

If that's the case, then the adding level too to the ORDER BY shouldn't make any difference unless your numeric order of versions and level are in sync.

A problem may appear if you have components like component2_name1.2 etc which could break this logic, for which you may require REGEXP to identify the required pattern. But, it doesn't appear so from your data and I assumed that to be the case and you may want to clarify if that's not what you may always have in your dataset.

Here's a demo of the result obtained for your sample data.

Demo

This will work of the numeric character is always a valid decimal and has only one decimal point. If you have complex versioning system like say 1.1.8, 2.1.1 etc, it needs far sophisticated ordering on top of REPLACE ( name, TRANSLATE(name,' .0123456789',' '),'').

You will find such examples in posts such as this one Here

Note: I would request you to also please read the instructions here to know how to ask a good question. This would avoid all confusion to people who try to understand and answer your question.

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • there is a main Category which named here car and computer. Second category which are the Component and the third Category, which are the second Component. What i want is, to "print out" the money which is less first and so on. BUT be aware of the categories. "print out" the first category if it's less, then its component and after that the other categories. if that what i typed makes any sense, i hope so – Mundo.Corjellito Oct 20 '18 at 16:03
  • @Mundo.Corjellito : I'm afraid that isn't any more clear to me. It appears to me your requirement keeps changing with every pass and you still are trying to figure out what you want when you say things like "*the money which is less first and so on.*" . I wish I could help you, but I won't because I can't put any more effort in trying to decipher the question. – Kaushik Nayak Oct 20 '18 at 16:51