Posted by David Portas on 07/22/07 09:33

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

[Back to original message]