• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Selecting from a table

 
Andras Lakos
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Guys ! Please,i am asking kindly your help regarding the selection from the following table. These are the command i want to execute.

First : I specify for example the Number: 777.Then, the cursor should go, select, and display all the record with that Number.
Second : It should move next to the min($Number) which is > then 777 select and display all record that much with that number.
Third : Same as Second instruction but this time it would be the min($Number) which is > 888 ...
NumberDesignation
777Java book
888Html book
888C++ book
777Css book
888Java Script book
999Python book
777C# book
999sql book


The result should be for the :
First instruction;
NumberDesignation
777Java book
777Css book
777C# book

Second instruction;
NumberDesignation
888Html book
888C++ book
888Java Script book

third instruction;
NumberDesignation
999Python book
999sql book

Thank You so much for any valuable answer in advance.
 
Knute Snortum
Bartender
Pie
Posts: 2887
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Usually in these kinds of situations, you have some sort of table describing what "Number" is.  So if Number represents a person...

NumberName
777Moby Dick
888Samuel Adams
999Mr. Bigg


Then you would join this table with the books table.  If you don't have something like the person table, it gets trickier, but I think you could do it with a sub select.
 
Andras Lakos
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your reply Mr Knute. Otherwise Yes ! i have a table like the person table you specified. So i guess i need to order first and map the inputs to my selection from the table person ! starting from the specified Number=777 . Could you kindly suggest for me a query for this please ! Thank you once again
 
Knute Snortum
Bartender
Pie
Posts: 2887
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Andras Lakos
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Last question please ! If i want to select with moving down on the table book using the order column person_id.

Can i write such a query :

NB: It should compulsory start from the specified book_id=777 in the example i provided. Regards
 
Knute Snortum
Bartender
Pie
Posts: 2887
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Almost.  A couple of things:

  • The WHERE clause needs to be before the ORDER BY clause
  • WHERE book.book_id = 777 will give you only one book


  • Do you really want ORDER BY person.person_name DESC?  This would mean the name "Zain" would be before the name "Applebee."

    Likewise, I'm not sure what "moving down" the table is, but if you want person_id 777, then 888, then 999, your WHERE clause should look like this:

     
    Andras Lakos
    Greenhorn
    Posts: 16
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thank you ! Perfect way to explain i appreciate. Otherwise, I used same condition as you wrote to me : But in my case i expect that whenever i run the sql command it gives one by one, It means the 777 record first, when i run second time the command it should give me the 888 record and at last the 999. that's what i wanted to explain by moving down on a table. Thank you once again.
     
    Andras Lakos
    Greenhorn
    Posts: 16
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thank you ! Perfect way to explain i appreciate. Otherwise, I used same condition as you wrote to me : But in my case i expect that whenever i run the sql command it gives one by one, It means the 777 record first, when i run second time the command it should give me the 888 record and at last the 999. that's what i wanted to explain by moving down on a table. Thank you once again.
     
    Knute Snortum
    Bartender
    Pie
    Posts: 2887
    62
    Chrome Eclipse IDE Java Postgres Database VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Are you going to write your program in Java?  If so, I would pull all the lines into a list, then check when the current ID isn't the same as the previous one.  Or, pull all the IDs into a list and issue separate queries, each with a WHERE clause equal to an ID.

    It might be good to step back and tell us what you are planning to do and why you need this requirement.
     
    Andras Lakos
    Greenhorn
    Posts: 16
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I am trying to build an application for education purpose written in java of course, that gets the first input of the $Number and then start to retrieve records following the other Ids whether they are > or <. the main thing is to read and display depending on the unique Id what is on the table starting from the specified id.
     
    Knute Snortum
    Bartender
    Pie
    Posts: 2887
    62
    Chrome Eclipse IDE Java Postgres Database VI Editor
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    If your "person" table isn't too large, I would read all the IDs into a Java TreeMap.  That way you can find a specific key and get the next higher or lower entry.  You could then use the SELECT and JOIN with a WHERE person_id = your key from the TreeMap.
     
    Andras Lakos
    Greenhorn
    Posts: 16
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thank you Mr Knute ! Yes,My table isn't large. I would prefer to read about the TreeMap. Thank you once again. regards
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic