CODEWITHSUNDEEP
c#sqloracle-databaselinqoracle10g
Agent007
Agent007

Reputation: 2750

GROUP BY, ORDER BY and taking first in LINQ

Suppose I have a table with two columns:

TABLE A
-------
ProjectID           NUMBER
STATUS              VARCHAR2(6) // either 'CLOSED' or 'NEW'

There could be maximum two entries for a ProjectID with the two possible values of STATUS and the combination (ProjectID, STATUS) is unique.

I need to select only those ProjectID's that have status 'NEW'. Also, if for a projectID, there are two entries with different statuses (NEW and CLOSED), I don't want it in the output.

I tried using group by, then ordering the resultset descending (so as to get 'NEW' row for a project ID first) and then taking the first row in LINQ, similar to this:

   var query = (from a in context.A.Where(o => o.STATUS == 'NEW')
               group a by a.ProjectID into groups
               select groups.OrderByDescending(o => o.ProjectID)
               .ThenBy(o => o.STATUS)
               .FirstOrDefault());

Butt it's resulting into an "APPLY" clouse in the query which is resulting into an error. Apparantly, Oracle 10g doesn't support it.

Any help is appreciated.

Upvotes: 2

Views: 194

Answers (3)

LolPython
LolPython

Reputation: 178

I believe I have accomplished what you want, using a subquery in LINQ.

var query = (from a in context.A
                         where (from b in context.A
                                where b.ProjectID == a.ProjectID
                                select new { a.ProjectID, a.STATUS }).Distinct().Count() == 0
                                && a.STATUS == "NEW"
                         select a.ProjectID).ToList();

Essentially, the outer query just makes sure that each a record has a NEW status, and the inner query makes sure that there are no two distinct records with the given ProjectID, because if there are, one is CLOSED. I avoided using a GROUP BY since you said your database does not support LINQ's way of doing it.

I hope I understood your problem correctly, and I hope this helps!

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

Proper tu use having count(distinct STATUS=1) :

create table tableA( ProjectID int, STATUS varchar2(10) );
insert all
  into tableA values(1    ,'NEW')
  into tableA values(1    ,'CHANGED')
  into tableA values(2    ,'NEW')
  into tableA values(3    ,'CHANGED')
select * from dual; 
/
select * from
(
select ProjectID, max(STATUS) STATUS
  from tableA
 group by ProjectID
having count(distinct STATUS)=1
)
where STATUS = 'NEW';

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143083

Something like this, perhaps?

SQL> with test (projectid, status) as
  2    (select 1, 'new'    from dual union     -- should be returned
  3     select 2, 'new'    from dual union
  4     select 2, 'closed' from dual union
  5     select 3, 'closed' from dual union
  6     select 4, 'new'    from dual           -- should be returned
  7    )
  8  select projectid
  9  from test
 10  group by projectid
 11  having min(status) = max(status)
 12     and min(status) = 'new';

 PROJECTID
----------
         1
         4

SQL>

Upvotes: 1

Related Questions