GROUP BY - CORRELATED SUB QUERY, DERIVED TABLE

[sanjdhiman]

Hi there I have an interesting question, which Im sure you guys can answer.

I have a set of data
Which has some duplicated data.

e.g.

EntityId CurrencyId Bid Offer Context
1 1 12 34 sdfasdfasdf
1 1 14 30 asdfasdfasdf
2 1 56 89 asdfad

Right so what I need is to return all fields, but I just want to group by the first 4 so I did a correlated sub query.

SELECT E.EntityID, E.CurrencyId, E.Bid, E.Offer, E.Context
FROM Table E INNER JOIN (SELECT EntityId, CurrencyID,
Bid, Offer
FROM Table E1
GROUP BY entityID, CurrencyId,
bid, offer
) AS E1
ON E.EntityID = E1.EntityID
AND E.CurrencyID = E1.CurrencyId
AND E.Bid = E1.Bid
AND E.Offer = E1.Offer


The correlated sub query returns just 2 rows. Which is correct.
However the whole query still returns the duplicated row as shown at the top (3 rows)..

Any ideas on how to make it work/

Thanks

Sanj

 

[bborissov]

What about (If the Context is not relevant and you want just some of them for duplicated records):

SELECT EntityID, CurrencyId, Bid, Offer, MAX(Context) AS Context
FROM Table
GROUP BY entityID, CurrencyId, bid, offer

Borislav Borissov

 

[sanjdhiman]

fantastic...that works great.

Thanks Borislave.

Its good to think of it in a more simpler way.

Cheers

 

[bborissov]

LOL
:-D

Borislav Borissov