Selecting newly occurred ID

[krshtej]

Hi,

I have a sql to select distinct values from a table. Problem is i need to extract the ID which is new.
i.e. say if there are two ID's for a student like xxxxK1 and xxxxK2
then i have to get the row where K2 is there. All other columns will be same only id will be like this. Distinct is picking both the rows. How to get only K2....

Thanks.
Krshtej.

 

[bborissov]

If ALL other columns have the same values:

SELECT MAX(Id) AS Id,
       other field list here
FROM YourTable
GROUP BY other field list here

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[krshtej]

Thanks bborissov. It worked.

 

[bborissov]

BTW why some student can have more than one ID?
This is wrong for me, but I don't know what is the reason, so maybe you are right.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[krshtej]

It is actually not student id it is the confirmation number when student register for a test. when he fails the test and reschedule it a new confirmation number will be given. when the report is ran it is giving out both the numbers and the request came to edit the report to show only new number. I tried ur code and it worked.
Thanks.

 

[bborissov]

O!
OK then

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.