oracle SQL subquery: using the minimum but getting all columns

Question

                       MIN(A.price)  CustomerID     TripID travelby

                                 25  x05               66 train
                                 66  x07               21 train
                                100  x07               12 train

Trying to figure this one out. the above results is from a query of 2 tables. However, I need to modify it so that it gives me the result of min price with all of its 4 columns.

this was my original sql:

(select min(price) from trips a, customers b where a.tripid = b.tripid and c.travelmode = 'train')

I can only get the row with 25, but that is only if i request the price column. How would you go about this, but get all columns? Thank you for all input

score 1 · Accepted Answer · answered Apr 23 '17 at 23:24

SELECT *
FROM   (
  SELECT *
  FROM   trips a
         INNER JOIN customers b
         ON ( a.tripid = b.tripid )
  WHERE  travelby = 'train'
  ORDER BY price ASC
)
WHERE  ROWNUM = 1;

or

SELECT *
FROM   (
  SELECT price,
         CustomerID,
         a.TripID,
         travelby,
         ROW_NUMBER() OVER ( ORDER BY price ASC ) AS rn
  FROM   trips a
         INNER JOIN customers b
         ON ( a.tripid = b.tripid )
  WHERE  travelby = 'train'
  ORDER BY price ASC
)
WHERE  rn = 1;

Or:

SELECT MIN( price ) AS price,
       MIN( CustomerID ) KEEP ( DENSE_RANK FIRST ORDER BY price, ROWNUM ) AS CustomerID,
       MIN( a.TripID   ) KEEP ( DENSE_RANK FIRST ORDER BY price, ROWNUM ) AS TripID,
       MIN( travelby   ) KEEP ( DENSE_RANK FIRST ORDER BY price, ROWNUM ) AS travelby
FROM   trips a
       INNER JOIN customers b
       ON ( a.tripid = b.tripid )
WHERE  travelby = 'train'

Thank you MT0. That worked. – DaviRoli Apr 23 '17 at 23:41 — DaviRoli, Apr 23 '17 at 23:41

score 0 · Answer 2 · answered Apr 23 '17 at 23:08

0

You can do something like this:

select . . .
from (select . . ., row_number() over (order by price desc) as seqnum
      from trips t join
           customers c
           on c.tripid = t.tripid and ?.travelmode = 'train'
     )
where seqnum = 1;

Your version of the query is quite confusing:

Why is it surrounded by parentheses?
What is c.travelmode? You haven't defined the table alias c.
Learn to use proper, explicit JOIN syntax.

answered Apr 23 '17 at 23:08

Gordon Linoff

1,242,037
58
646
786

Gordon, thank you for the input. the parenthesis was there because i just copied it from another query within a query in sql developer. forgot to removed them. travelmode was actually meant as travelby. – DaviRoli Apr 23 '17 at 23:43

oracle SQL subquery: using the minimum but getting all columns

2 Answers2