CODEWITHSUNDEEP
sqloracle-databasesubquery
Arun
Arun

Reputation: 3841

Need a query without using subquery

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?

Upvotes: 0

Views: 157

Answers (4)

Shapka
Shapka

Reputation: 21

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

Upvotes: -1

Ben
Ben

Reputation: 52913

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

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67792

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

Upvotes: 1

Sashi Kant
Sashi Kant

Reputation: 13465

Try this ::

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

Upvotes: -1

Related Questions