-2

I'm having two tables named as
1.)Primary_Table
2.)Secondary_Table

as shown below:-

Primary_Table                          Secondary_Table
Sl_no Sub_id  Destination              Sub_id   Price 
1       1000  New York                  1000    1987 
1       1001  Tokyo                     1001    5679 
1       1002  London                    1002    7875 
2       1003  Mumbai                    1003    6789 
2       1004  Sydney                    1004    7489 
2       1005  Munich                    1005    6746

Here I'm trying to write a SQL/MYSQL query to find the maximum and minimum price with respect to the Sl_no, which is in primary table.

Community
  • 1
  • 1
Jhon
  • 27
  • 4
  • 2
    What have you tried so far? Hint: you'll need to join the two tables, before using aggregate MIN/MAX functions on the data. – Boneist Dec 16 '16 at 11:57
  • 2
    Which RDBMS is this for as you have tagged both Oracle and MySQL? – MT0 Dec 16 '16 at 12:00
  • I cannot join two rows and create a new table as I'm writing a procedure and I need a SQL query which will fetch the required data. – Jhon Dec 16 '16 at 12:28
  • @Jhon where was it mentioned you'd need to create a table? Your first task would be to write a query that joins the two tables and provides output that you can then group on the min/max price per sl_no. – Boneist Dec 16 '16 at 14:02

2 Answers2

0

You can go with this solution.

SELECT
     tb1.sub_id
   , tb1.sl_no
   , tb1.destination 
   , tb2.price
FROM
  tb1
JOIN
  tb2
ON
  tb2.sub_id = tb1.sub_id
WHERE
  price = (SELECT MIN(price) FROM tb2) OR price = (SELECT MAX(price) FROM tb2)

Hope this will solve your problem.

Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
Manoj Sharma
  • 1,467
  • 2
  • 13
  • 20
0

This query will give you max and min price for each Sub_id

SELECT Sub_id,MIN(Price) AS minimum, MAX(Price) AS maximum FROM 
(SELECT p.si,p.sub,s.price FROM Primary_Table as p JOIN  Secondary_Table as s ON  
p.Sub_id=s.Sub_id) AS t GROUP BY t.Sub_id

SQLFiddle

jophab
  • 5,356
  • 14
  • 41
  • 60