| .NET DotNet Forum Index » ADO .NET Forum » linq and SQL query... |
|
Page 1 of 1 |
|
| Author |
Message |
| Chris... |
 Posted: Sun Oct 11, 2009 10:33 am |
|
|
|
Guest
|
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
---------------------
1 A 10
2 D 13 |
|
|
| Back to top |
|
|
|
| Patrice... |
| Posted: Sun Nov 01, 2009 3:10 pm |
|
|
|
Guest
|
"Chris" <Chris at (no spam) discussions.microsoft.com> a écrit dans le message de
news:3D4E87A8-1488-48F3-9BA2-59C8F17C3326 at (no spam) microsoft.com...
Quote: 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
---------------------
1 A 10
2 D 13
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)...
--
Patrice |
|
|
| Back to top |
|
|
|
| go... |
| Posted: Tue Nov 03, 2009 7:01 am |
|
|
|
Guest
|
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:
Quote:
"Chris" <Chris at (no spam) discussions.microsoft.com> a écrit dans le message de
news:3D4E87A8-1488-48F3-9BA2-59C8F17C3326 at (no spam) microsoft.com...
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
---------------------
1 A 10
2 D 13
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)...
--
Patrice |
|
|
| Back to top |
|
|
|
| go... |
| Posted: Tue Nov 03, 2009 7:19 am |
|
|
|
Guest
|
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:
Quote: 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:
"Chris" <Chris at (no spam) discussions.microsoft.com> a écrit dans le message de
news:3D4E87A8-1488-48F3-9BA2-59C8F17C3326 at (no spam) microsoft.com...
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
---------------------
1 A 10
2 D 13
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)...
--
Patrice |
|
|
| Back to top |
|
|
|
|
