[RESOLVED] [SQL Server] - Query Distinct and Max or Function

[mickey_pt]

Hello

I'm having some doubts to build a query (if it possible)...

I have this table,
idReg (PK + AutoInc), FieldFK, InternalNumber,Name,Qt
----------------------------------------------------------------------
1 | 8 | 1111 | Prod1 | 123.3
2 | 7 | 2222 | Prod2 | 222.2
3 | 8 | 111A | Prod1Renamed | 100
4 | 2 | 4444 | Pord9 | 12
5 | 7 | 3333 | Prod2 | 333

What i want from this table it's all the rows with Distinct FieldFK, with the idReg MAX...

The query result would be (not necessarly by this order):
3 | 8 | 111A | Prod1Renamed | 100
4 | 2 | 4444 | Pord9 | 12
5 | 7 | 3333 | Prod2 | 333

I'm trying to build the query, but i don't get anywhere...
Distinct only works if i only select the FieldFK if i had the other fields i get all the rows because it have distinct values for the other columns. What i need it's a kind of max for each FieldFK.

[si_the_geek]

You are on the right lines - what you want isn't a Distinct or any kind of Group By, and "a kind of max for each FieldFK." is pretty much it.

The way to do that is to use a sub-query (using the same table) based on FieldFK, which you can do like this:

SELECT idReg, FieldFK, InternalNumber, Name, Qt
FROM tableName as t1
WHERE idReg = (SELECT Max(idReg)
               FROM tableName
               WHERE FieldFK = t1.FieldFK)

By giving the table an alias in the main query, you can refer to the values from it in the sub-query, thus causing the sub-query to run for each row of the main query (and be linked to it).

[mickey_pt]

Thanks, i allready manged to do the job, not equal to your solution but similar... Sometimes it helps writing the problem

Resolved