linq and SQL query

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Imagine there is a DB table:

col1 col2 col3
---------------------
1 A 10
1 B 11
1 C 12
2 D 13
2 E 14
2 F 15

How to write both sql and linq queries (I DONT want to use a subquery) to
get rows - min of col3 groupped by col1 BUT WITH col2:

the result should be:

col1 col2 col3
 
Chris said:
Imagine there is a DB table:

col1 col2 col3
---------------------
1 A 10
1 B 11
1 C 12
2 D 13
2 E 14
2 F 15

How to write both sql and linq queries (I DONT want to use a subquery) to
get rows - min of col3 groupped by col1 BUT WITH col2:

the result should be:

col1 col2 col3
Click to expand...

And col2 is also the min value ? For now it looks to me you group by col1,
with the MIN aggregate function for both col2 and col 3 (i. just adding
MIN(col2) to your query would be enough)...
 
In sql it should be something like (if col3 is id and you want first
record of col1)

select * from YOURTABLE a where col3 not in (select top 1 b.col3 from
YOURTABLE b where a.col1 = b.col1 and a.col2 = b.col2)

haven't tested it, but there's something to start with.

///M

Patrice skrev:
 
well, it should be
select * from YOURTABLE a where col3 in (select top 1 b.col3 from
YOURTABLE b where a.col1 = b.col1)

sorry for the f**kup
///M

go skrev:
 
Back
Top