Database

Devesh Sinha wrote:

Please provide me with some sample proc.

update pr 
set rostEntID =
(
    select TOP 1 e.recid
    from @Existing e, @dupes d2
    where
            e.refid = d2.refid
        and e.palias = d2.palias
        and e.palias2 = d2.palias2
        and e.pweight = d2.pweight
        and e.pHandicap = d2.phandicap
        and e.loginID = d2.loginid
        and e.password = d2.password
)
FROM participantresponses pr 
INNER JOIN @dupes d
ON pr.rostEntID = d.recID

SELECT MAX(TheDate), Type, Value 
FROM MyTable 
GROUP BY Type, Value

SELECT MAX(TheDate), Type
FROM   MyTable
GROUP BY TYPE

WITH maxdate AS (
    SELECT MAX(TheDate) TheDate, Type
    FROM MyTable
    GROUP BY Type
    )
SELECT  t.TheDate, t.Type, t.Value
FROM    MyTable t join maxdate d
    ON  t.TheDate = d.TheDate
    AND t.Type = d.Type

SELECT  TheDate, TYPE, Value
FROM    (
        SELECT TheDate, Type, Value, Rank( ) OVER (PARTITION BY Type ORDER BY TheDate DESC NULLS LAST) RN
        FROM MyTable
        )
WHERE   RN = 1

select distinct
(select top 1 t2.thedate from MyTable as t2 where t2.[type]=t1.[type] order by t2.thedate desc) as thedate
, t1.[type]
,(select top 1 t2.value from MyTable as t2 where t2.[type]=t1.[type] order by t2.thedate desc) as [Value]
from MyTable as t1
order by t1.[type]

select Value
      ,Type
  from TABLE
 where TheDate in ( select MAX(TheDate)
                      from Table
                     group by Type
                  )
;

MikeDhaan wrote:

and not the max date per type

select date
      ,type
      ,value
  from table t
      , ( select max(date) max_date
                ,type type
            from Table 
           group by type
        ) tg
 where t.date = tg.max_date
   and t.type = tg.type
order by type
;