2 Replies - 238 Views - Last Post: 16 August 2018 - 09:01 AM

#1 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

How would you display min to max # on each box?

Posted 16 August 2018 - 07:19 AM

I am trying to display all info in one query.

So table A has item_no, box_no, lot_no, transfer_no

and

Table B has item_no, box_no, lot_no, content_no

and

Table C has box_no, transfer_no

So on result I want to display

item_no, lot_no-min content no-max content no, box_no, transfer_no in one line without displaying entire list of box content. Each box has 20 contents. so example line should look like

item_no lot_no box_no transfer_no
1 A-001-020 1 2018-08-01
2 A-021-040 2 2018-08-03
3 A-041-060 3 2018-08-15




like that. when I joined table but I don't know how to display min and max in one line like that.

select distinct a.item_no, (b.lot_no || ' - ' || select min(content_no) ... || ' - ' || select max(content_no)
, box_no, transfer_no

from table1 a, table2 b, table3 c

where 
a.item_no = b.item_no
and a.transfer_no = c.transfer_no
and a.transfer_date between '2018-08-01' and '2018-08-10';


This post has been edited by shin777: 16 August 2018 - 07:23 AM


Is This A Good Question/Topic? 0
  • +

Replies To: How would you display min to max # on each box?

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14862
  • View blog
  • Posts: 59,297
  • Joined: 12-June 08

Re: How would you display min to max # on each box?

Posted 16 August 2018 - 07:37 AM

You may need to look at subqueries to join off of for the MIN/MAX aggregates on 'item numbers'.
Was This Post Helpful? 0
  • +
  • -

#3 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: How would you display min to max # on each box?

Posted 16 August 2018 - 09:01 AM

yep. found the answer.. it was group by that had problem. :(
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1