CODEWITHSUNDEEP
sqlsql-serversql-server-2012aggregate-functions
DoIt
DoIt

Reputation: 3438

returning only one row for each value of a column along with other values in different columns

I am working on a query on a SQL table which has several columns along with several rows of data and the query returns one row for each unique first and second columns based on the criteria given in the query.

For Example, I have the following table CC

product  term   bid  offer bidcp offercp
AA       sep14  20    10    x   y
AA       Sep14  15     9    p   q  
BA       Sep14  30    15    as  ps
XY       Sep14  25    15    r   t
XY       Oct14   30   20    t    r
XY       Oct14   25   22    p   q

When I run the query on the above table it should return the following data

product  term   bid  offer bidcp offercp
AA       sep14  20    9      x   q(coming from a record which has lowest offer) 
BA       Sep14  30    15      as  ps
XY       Sep14  25    15      r   t
XY       Oct14  30    20      t    r

When I executed the following query it grouped the data in CC even by bidcp and offercp and returned almost all the rows as both offercp and bidcp are unique in one or the other way but I just wanted bidcp and offercp to be where bid and offer are coming from assuming pair of both bid and offer are unique for each product and term

select product,term,max(bid) as bid,min(offer) as offer,bidcp,offercp from canadiancrudes where product like '%/%' group by product,term,bidcp,offercp

But, when I removed bidcp and offercp from groupby clause it threw me an obvious error

Column 'CC.BidCP' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there a better way to fix it?

Upvotes: 0

Views: 99

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

In that case, you need 2 CTEs:

WITH o AS (
      SELECT product,term,offer,offercp, ROW_NUMBER() OVER (PARTITION BY product, term ORDER BY offer ASC) AS rn
      FROM  canadiancrudes where product like '%/%'
)
,   b AS (
      SELECT product,term,bid,bidcp, ROW_NUMBER() OVER (PARTITION BY product, term ORDER BY bid DESC) AS rn
      FROM  canadiancrudes where product like '%/%'
)
    SELECT o.product,o.term,b.bid,o.offer,b.bidcp,o.offercp 
    FROM o
    INNER JOIN b 
      ON o.product=b.product
      AND o.term=b.term
    WHERE o.rn=1
    AND b.rn=1

Upvotes: 1

TMNT2014
TMNT2014

Reputation: 2130

Use a CTE to get the min, max value - 

WITH MaxMin_CTE AS (
   SELECT product,term,max(bid) as bid,min(offer) AS Offer
   FROM CC
   GROUP BY product,term)
SELECT * from CC 
INNER JOIN MaxMin_CTE ON CC.product = MaxMin_CTE .product
AND CC.bid= MaxMin_CTE.bid AND CC.Offer = MaxMin_CTE.offer
AND CC.Term = MaxMin_CTE.Term

Heres the SQL fiddle - http://sqlfiddle.com/#!6/a6588/2

Upvotes: 0

Related Questions