• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

query about sub query see scenario need some advice

 
chrstian ferma
Ranch Hand
Posts: 72
Eclipse IDE Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good day guys , what i want to do was i have table named itemList having words that is starting in alphabet , what i want was to retrieve first all the item from A to Z except letter P but i will retrieve letter P after i retrieve A to Z
see what i did

(in this line i already retrieve all the items from a to z except p now i need to retrieve all the items start with P)
(this is what i want but is it possible to use this two queries in one query only?)

this is what im thinking but it's wrong



expected ouptut:
bulb
chair
door
kite
sack
xItem1
xItem2
xItem3
pitem1
pitem2
pitem3

this is the output that im aiming first retrieving "a" to "z" except "p" the retrievieve "p" after the others , can i do this retrieving all the data in in one query inorder like my expected output? thank you

 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are several ways to approach this, depending on your database's SQL dialect. You can order by several different columns, including values you derive in your SELECT, so you could do something like the following:

This gets the first character of the item_name and uses this to tell the SELECT to put the non-P entries first, followed by the P entries, then order by item_name within each sub-set

NB: I don't have a database handy, so my SQL may be slightly dodgy there, but you can look this up online for your specific database.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic