2

I have two tables. Title and Order. Title has title_num. Order has title_num and order_num. Each title has many orders.

I would like to display title_num and all of its orders. But I would like to display it so that I display 1 title_num for multiple order_nums:

title_num| order_num
1          a
1          b
1          c
2          x
2          y

I would like it to look like:

title_num| order_num

1          a
           b
           c

2          x
           y

I've tried using left join to sort it by the title but that's where I'm stuck. Is it possible to display it like this?

select t.title_num, o.order_num
from title t
left join order o
on t.title_num=o.title_num
Improve this question
1
  • If you were using SQL*Plus for this then you could use the break functionality described in the docs to achieve this, including the gap between the values (e.g. break on title_num nodup skip 1). That doesn't seem to have been carried over to SQL Developer unfortunately. Commented May 29, 2013 at 6:37

3 Answers 3

Reset to default
3

(Don't have enough rep to comment, so I'll give the options in an answer)

You can't hide a result if its the same as the previous row. Instead, you would handle it in the code or report.

  • If you are displaying it in a report, you would group the results in the report manager.

  • If you are iterating in code, have a variable that keeps track of that column variable.

    Initialize the variable with a blank string. Then as you iterate the query's dataset:

    1. If the current row value is the same as the variable's value, display a blank. Otherwise, show the row value.
    2. Set the variable to the current row's value.
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Yes, displaying it in the report itself was a lot easier than trying to get my SQL query to work. Thanks.
Excellent, glad I could help! (Cough Cough.. mark mine as answer ;) )
2

You can do this with the LAG function and a CASE statement.

SELECT CASE WHEN LAG(title_num,1) OVER (PARTITION BY title_num ORDER BY order_num) IS NULL THEN title_num
              ELSE ' '
         END
      ,order_num
FROM Order

SQL Fiddle Demo

Improve this answer

1 Comment

I tried this first but I also had other parameters to display too so I give up on it. Luckily I got it to work in my report somehow. Thanks for your help though, I learned a lit bit more about my queries after trying to work this code though so it was still helpful.
1

Use the window function (analytic function in Oracle parlance) row_number():

SELECT CASE WHEN row_number() OVER (PARTITION BY title_num
                                    ORDER BY order_num) = 1 THEN
       title_num ELSE NULL END AS title_num
      ,order_num
FROM   ord o
ORDER  BY  o.title_num, order_num;

->SQLfiddle.

Why do you JOIN to title at all? Are there titles without order? Otherwise you can just omit it from the query.

Don't use reserved words like order as table names. I use ord instead in my solution.

Note how I table-qualify o.title_num in ORDER BY to refer to the input column, not the output column.

Improve this answer

1 Comment

I was using join because I had more parameters from both tables that needed to be displayed. But I think I got the results I wanted. After seeing your code I realize I was over thinking the problem. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.