how to fetch the last inserted (datetime) records for each category in database table

how to fetch the last inserted (datetime) records for each category in database table

like:

select top 1 from table1, table2

where condition1 ...

and condition2 = case when (@var1 = null) 开发者_开发问答then table2.condition2 else @var1

-- for all categories if a specific one is not passed

order by Creation_Date

Any help is appreciated (esp. sample code). Thanks in advance.

Use GROUP BY, like

SELECT
  MAX(Creation_Date),
  Category
FROM table
GROUP BY Category

to exclude any records in "future" add Creation_Date < your_max_date condition into WHERE.

I would use analytical functions

SELECT *
FROM
(
  SELECT ROW_NUMBER() OVER (PARTITION BY category ORDER BY Creation_Date DESC) RN, *
  FROM table
  WHERE condition1
) T
WHERE T.RN = 1

how about :

SELECT MAX(Creation_date), category
FROM table1, table2 
WHERE condition1 ..
GROUP BY category

This will let you return the full record. You need to be careful with GROUP BY, as it will return the MAX date, but will return the record which is encountered first in the table.

SELECT MAX(a.dtm_created),a.*
FROM (
       SELECT * FROM table1,table2 WHERE someconditions 
       ORDER BY table1.dtm_created DESC) AS a
GROUP BY a.category