linq and SQL query

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
 
P

Patrice

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

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)...
 
G

go

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:
 
G

go

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:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top