CODEWITHSUNDEEP
sqldatabaseoracle-database
Vivek Anand
Vivek Anand

Reputation: 13

Need a SQL query for this case

I have the following table

OrderNO   |    ParentNO
1         |        null
2         |        null
1         |       3
2         |       3
3         |       null

I need a SQL query which would retrieve the above table data in the following order

OrderNO   |    ParentNO
    1         |        null
    2         |        null
    3         |       null 
    1         |       3
    2         |       3

The logic behind this view is first preference goes to the Order no with null , if a order no is available in parent no then it should displayed next to the respective order no.In the above sample - orderno1 with null comes in the first row then since orderno1 is not available in parentno then next orderno2 with null parentno is displayed since orderno2 is not available in parentno then next orderno3 with null parentno is displayed then since orderno3 is available in parentno the respective rows is displayed next 3.1 ( 1 | 3) , next 3.2 (2 | 3)

Upvotes: 1

Views: 50

Answers (3)

Robert
Robert

Reputation: 25753

You can use NULLS FIRST as below

select OdrerNO, ParentNO
from tab
order by ParentNO NULLS FIRST, OdrerNO ASC

Upvotes: 1

Sam
Sam

Reputation: 948

Try:

Select * FROM TABLE
ORDER BY ParentNo, OrderNo

Nulls should come first if I remember rightly. So ordering it by parentNo will give the Nulls first, then order the orderNo. So will give the desired effect.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

Try this query:

SELECT OrderNo, ParentNo FROM
table
ORDER BY ParentNo ASC NULLS FIRST, OrderNo ASC

You need to specify NULLS FIRST on thd ParentNo column because by default NULL values go last in an ascending sort.

Upvotes: 1

Related Questions