[T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

re: [T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

... — Sat, 07 Mar 2009 03:38:14 GMT

Gute Arbeit hier! Gute Inhalte.

re: [T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

paulains — Wed, 31 Dec 2008 21:47:36 GMT

Thanks for your example... I would have been totally lost trying to figure out how to do this sort of thing with traditional t-sql statements.

re: [T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

Andrew Rimmer — Mon, 17 Nov 2008 19:50:31 GMT

You can find some more options for this here (including your solution).

stackoverflow.com/.../what-is-the-best-query-to-get-the-current-records-in-an-archive-table-sql-serve

re: [T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

Sandy — Thu, 28 Aug 2008 12:44:57 GMT

Hi,

Here is the code with rownum....

SELECT [column names] FROM(SELECT ROWNUM RES_ROWNUM,[column names] FROM(SELECT * FROM TL_FLEXCAB th where exists ( select 1 from TL_FLEXCAB thx where th.FL_FNN_JOIN = thx.FL_FNN_JOIN group by thx.FL_FNN_JOIN having max( thx.FL_DT_TM ) = th.FL_DT_TM )))A WHERE A.RES_ROWNUM > 0 and A.RES_ROWNUM <=100

re: [T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

Sandeep — Thu, 28 Aug 2008 10:40:25 GMT

Hi i tried your code. It worked but i want to use rownum with this query as well. Can you give me the code with rownum.

following is the code...

SELECT * FROM tl_flexcab th where exists ( select 1 from tl_flexcab thx where th.FL_FNN_JOIN = thx.FL_FNN_JOIN and FL_OWN_CD = 'ZAG' group by thx.FL_FNN_JOIN having max( thx.FL_DT_TM ) = th.FL_DT_TM )

re: [T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

Lavita — Thu, 07 Aug 2008 03:49:19 GMT

Hi Jon,

The information above is a bit helpful in terms of starting to understand the logic of structuring a joint query.

However, the example above is not working for me. I need to structure a nested query for one table which contains historical data to pull various records with the most recent update date without pulling duplicate records and I cannot seem to structure the query correctly. If possible, would you please provide an example of this type of query?

- Lavita

re: [T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

Glen — Wed, 27 Feb 2008 11:10:39 GMT

I am getting the error that RANK() is not a valid function. I am using MSSQL2000. Anyways, here is my situation.

I have a table Stories (ID, Heading, SubHeading,..., EffectiveDate)

When a story is updated, the Heading and SubHeading never change and a new record is inserted and the old one is history.

ie:

ID | Heading | SubHeading | EffectiveDate

55 | testing | testing123 | 22 Feb 2008

56 | testing | testing123 | 26 Feb 2008

58 | different | testing456 | 26 Feb 2008

I need to list all the lastest stories only. So I should get the last 2 records shown above. There could be multiple entries for each "heading & subheading" and when I list, I need all the items where the effectivedate is the latest...

What is the solution?

re: [T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

guyishappy — Tue, 24 Jul 2007 05:44:48 GMT

thanks i like your code fun time at me pants

re: [T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

David L. Penton — Fri, 13 Jul 2007 14:43:47 GMT

@Jon: If you start by joining to a subquery and need to add fields later, it can get out of control quickly.

Can you provide an example of this? Based on your statement of "The general idea is to write a query which gets the latest update date" then the RANK/PARTITION feature is likely overkill and possibly more confusing.

SELECT

p.[Name],

p.ProductNumber,

th.TransactionDate,

th.TransactionID

FROM

Production.TransactionHistory th

INNER JOIN

Production.Product p

ON p.ProductID = th.ProductID

where

exists (

select 1

from Production.TransactionHistory thx

where

-- this is our local 'primary key'

th.ProductID = thx.ProductID

group by

-- group by our local 'primary key'

thx.ProductID

having

-- what predicate are we after?

max( thx.TransactionDate )

= th.TransactionDate

)

To me, based on your requirements, and any requirement that is based on a MAX/MIN/SUM (basically simple aggregates) then this particular format of query it far superior and likely a better performer. Where the RANK/PARTITION would be superior would be cases where you need the TOP X of something over a range of data, or some other subset of that data, or possibly if your data in your actual search predicate could be duplicated as well (as having duplicate datetime values by ProductID in the example) - but the latter could be handled easily in the outer query.

re: [T-SQL] Getting distinct, current entries from tables with timestamp / datetime fields

BAlexandrov — Fri, 13 Jul 2007 09:30:23 GMT

It seems that you wanted to use row_num instead of rank because if you have duplicate records by TransactionDate you would get duplicates if you dont have duplicates, rank is not useless...