Need a query without using subquery

Question

My table is

PROPOSAL_TABLE
  PID   QUOTE_ID   PDF1
  ---   --------   ----
   1       123      null
   2       123      null
   3       123      null
   4       152      null
   5       888      null

I need to select Quote ID with MAX PID. I have a query:

 SELECT PROPOSAL_PDF FROM PROPOSAL_TBL WHERE QUOTE_ID = '123' 
 AND PID = (SELECT MAX(PID) FROM PROPOSAL_TBL WHERE QUOTE_ID = '123')

How can I get the value without using sub query?

score 2 · Answer 1 · edited Sep 05 '13 at 12:08

Without using a sub-query you can use the KEEP clause:

select max(pid) as pid
     , max(quote_id) keep ( dense_rank first order by pid desc ) as quote_id
     , max(pdf1) keep ( dense_rank first order by pid desc ) as pdf1
  from proposal_table
 where quote_id = '123'

This should be highly efficient but as always test. I would recommend reading Rob van Wijk's blog post on it.

The standard alternative would be to use a sub-query but not require another scan of the table by using an analytic function, e.g. ROW_NUMBER()

select pid, quote_id, pdf1
  from ( select a.*, row_number() over ( order by pid ) as rn 
           from proposal_table a
          where quote_id = '123' )
 where rn = 1

score 1 · Answer 2 · answered Sep 05 '13 at 12:03

You can use analytics:

SQL> WITH DATA AS (
  2            SELECT 1 pid, 123 quote_id, 'A' pdf1 FROM DUAL
  3  UNION ALL SELECT 2 pid, 123 quote_id, 'B' pdf1 FROM DUAL
  4  UNION ALL SELECT 3 pid, 123 quote_id, 'C' pdf1 FROM DUAL
  5  UNION ALL SELECT 4 pid, 152 quote_id, 'D' pdf1 FROM DUAL
  6  UNION ALL SELECT 5 pid, 888 quote_id, 'E' pdf1 FROM DUAL
  7  )
  8  SELECT DISTINCT first_value(pid) over (ORDER BY pid DESC) pid,
  9                  quote_id,
 10                  first_value(pdf1) over (ORDER BY pid DESC) pdf1
 11    FROM DATA
 12   WHERE quote_id = 123;

       PID   QUOTE_ID PDF1
---------- ---------- ----
         3        123 C

You can also use aggregates:

SQL> WITH DATA AS (
  2            SELECT 1 pid, 123 quote_id, 'A' pdf1 FROM DUAL
  3  UNION ALL SELECT 2 pid, 123 quote_id, 'B' pdf1 FROM DUAL
  4  UNION ALL SELECT 3 pid, 123 quote_id, 'C' pdf1 FROM DUAL
  5  UNION ALL SELECT 4 pid, 152 quote_id, 'D' pdf1 FROM DUAL
  6  UNION ALL SELECT 5 pid, 888 quote_id, 'E' pdf1 FROM DUAL
  7  )
  8  SELECT MAX(pid),
  9         quote_id,
 10         MAX(pdf1) KEEP (DENSE_RANK FIRST ORDER BY pid DESC) pdf1
 11    FROM DATA
 12   WHERE quote_id = 123
 13   GROUP BY quote_id;

  MAX(PID)   QUOTE_ID PDF1
---------- ---------- ----
         3        123 C

score -1 · Answer 3 · answered Sep 05 '13 at 12:01

-1

Try this ::

SELECT PROPOSAL_PDF 
FROM PROPOSAL_TBL WHERE QUOTE_ID = '123' 
ORDER BY  PID desc limit 1

answered Sep 05 '13 at 12:01

Sashi Kant

13,277
9
44
71

Not Working "SQL command not properly ended!". 'limit 1' is causing the problem – Arun Sep 05 '13 at 12:06
Not valid syntax for Oracle – Dave Costa Sep 05 '13 at 12:07
This is a valid Query for MYSQL to get the TOP 1 record, in Oracle we have something like ROWNUM, appreciate if you give this a shot – Sashi Kant Sep 05 '13 at 12:12

Shapka · Answer 4 · 2013-09-06T09:10:25.097

-1

Sql Server

SELECT TOP 1 PROPOSAL_PDF  
FROM PROPOSAL_TBL 

WHERE QUOTE_ID = '123' 
ORDER BY  PID desc

Oracle

SELECT * from (Select * FROM PROPOSAL_TBL 
               WHERE QUOTE_ID = '123' 
               ORDER BY  PID desc)
WHERE ROWNUM <=1

edited Sep 06 '13 at 09:10

answered Sep 05 '13 at 12:26

Shapka

21
5

It's because you can't limit a query to the first value in the result-set before the results have been ordered: http://stackoverflow.com/questions/9175749/ora-00907-missing-right-parenthesis-issue-select-with-order-by-inside-insert-q/9177669#9177669 – Ben Sep 06 '13 at 06:25
i used for sql server not tried oracle one sorry about that u need oracle – Shapka Sep 06 '13 at 09:04
your answer with inner sql i think http://stackoverflow.com/questions/3451534/how-to-do-top-1-in-oracle – Shapka Sep 06 '13 at 09:06

Need a query without using subquery

4 Answers4