Urgent hlep SQL-TSQL

[Naveed Kamboh]

Hi Friends, I have a table with fileds ID,code,descripiton, date where ID is primaryKey also date is a unique while code and description could hame same data. my problem is i have to fetch last two records for each of the same code and description in the table. How can i do that please? Please not that "select top 2 * from table1 order by id des"c is returning only last 2 recods of the table. While i need last 2 records for each of the code and description in the table. thanks Naveed Kamboh

[SeMartens]

Hi, okay I will try my best, so I hope this will help you. My idea is to use a Stored Procedure or something like this, where you build a loop over all value-pairs of code and description (can be retrieved by a distinct or a group-by). And inside that loop you select the last two records using a where-condition to identify the matching records. Afterwards you can put these selected rows into a temporary table. Perform a select at the end to retrieve the result. Pseudo-code: create temporary table X (must have same scheme like your original table) foreach value-pair E of code and description begin insert into X select top 2 * from original_table where code = E.code and description = E.description end select * from X Hmmm, i hope this helps Regards Sebastian -- modified at 9:02 Tuesday 6th June, 2006

[Michael Potter]

Assuming MS SQL, you could try something like this:

SELECT
    t.*
FROM
    table1 t
WHERE
    t.date = (SELECT MAX(date)
              FROM table1
              WHERE code = t.code and
              description = t.description) 
    OR
    t.date = (SELECT MAX(date)
              FROM table1
              WHERE code = t.code and
                    description = t.description AND
                    date < (SELECT MAX(date)
                            FROM table1
                            WHERE code = t.code and
                                  description = t.description))

[Ricardo Casquete]

We had a problem like this, some time ago... and the solution were to use stored procedured with cursors.... ( The performance.... we know ) What we did is to build the cursor at runtime, with the Execute () Method... setting here the Order By... after that create the Cursor and Fetch till the Value needed... aslo notice you can specify the cursor to fetch directly a row Alter Procedure DoLoop as Declare curMyLoop Cursor For Select * from spt_values Open curMyLoop Fetch Next from curMyLoop while @@Fetch_status = 0 Begin Fetch Next from curMyLoop End deallocate curMyLoop Go alter procedure pp as declare @str varchar(50) set @str= 'Select * from spt_values' Execute ( @str ) Go Regards Ricardo Casquete

[Stephan Samuel]

That's by far your best option. There's a way to do it with grouping also which may come out faster depending on the DBMS and index structure. Either way, this is a much better solution than using a cursor.

[Naveed Kamboh]

Thanks to all, It realy helped me. Naveed Kamboh