Get one row from each group of rows

Hi,

I'm trying to build a query that get only one row from a group of
rows, but I need the values from that row and not the results of one
function group.
I need one row for each idRef, with column2=2 and the bigger column1

id |idRef | column1 | column2
1 1 0 1
2 1 1 2
3 1 2 1
4 2 0 1
5 2 1 2
6 2 2 1
7 2 3 2

For these, I will take the rows with id=2 and id=7.

Thank you, and sory for my english.

Re: Get one row from each group of rows

On 22 Jul, 05:19, deluca.vice...@gmail.com wrote:
> Hi,
>
> I'm trying to build a query that get only one row from a group of
> rows, but I need the values from that row and not the results of one
> function group.
> I need one row for each idRef, with column2=2 and the bigger column1
>
> id |idRef | column1 | column2
> 1 1 0 1
> 2 1 1 2
> 3 1 2 1
> 4 2 0 1
> 5 2 1 2
> 6 2 2 1
> 7 2 3 2
>
> For these, I will take the rows with id=2 and id=7.
>
> Thank you, and sory for my english.

The following assumes that there is only one row where column2 = 2 and
column1 is the largest value - as would be the case if (idRef,
column1, column2) was a key for example. If you include DDL with keys
in future posts then people who respond won't have to guess which
columns are unique.

SELECT id, idRef, column1, column2
FROM tbl AS t1
WHERE column2 = 2
AND column1 =
(SELECT MAX(column1)
FROM tbl
WHERE idRef = t1.idRef
AND column2 = 2);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).as px
--

Re: Get one row from each group of rows

On Jul 22, 9:19 am, deluca.vice...@gmail.com wrote:
> Hi,
>
> I'm trying to build a query that get only one row from a group of
> rows, but I need the values from that row and not the results of one
> function group.
> I need one row for each idRef, with column2=2 and the bigger column1
>
> id |idRef | column1 | column2
> 1 1 0 1
> 2 1 1 2
> 3 1 2 1
> 4 2 0 1
> 5 2 1 2
> 6 2 2 1
> 7 2 3 2
>
> For these, I will take the rows with id=2 and id=7.
>
> Thank you, and sory for my english.

select a.* from tbl a
join
(select idref,max(column1) as column1
from tbl
where column2 = 2
group by idref) as b
on a.idref = b.idref
and a.column1 = b.column1