Interesting DISTINCT SELECT query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Interesting DISTINCT SELECT query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-16 : 08:08:27

Ben writes "Hi,

I need a query that retrieves a DISTINCT set from a huge table.
The table is of the form:

RecordNum | Date | Time | State

I want to retrieve the last entry for a each recordnum in a set of record numbers. There could be up to 30000 distinct Recordnumbers, each appearing between 0 and 50 times an hour.

The use of this one SQL statement, would prevent the requirement for looping over the table (order by date and time descending) looking for each recordnum in the required subset.

The paradox is that we want to retrieve a distinct RecordNum and its corresponding state, but this must be time dependent, i.e. the last appearance of a recordnum before a specified time.

Best Regards,

Ben"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-16 : 09:32:47

Something like

Select * from yourTable where time in (select max(time) from yourTable group by RecordNum,date)

Madhivanan

Failing to plan is Planning to fail

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-16 : 10:06:48

This query is complicated because you have different columns for Date and Time (assume both are VARCHAR?). It would be better to CAST them together as a DATETIME column for this query.


SELECT M.RecordNum, M.Date, M.Time, M.State
  FROM MyTable M
  INNER JOIN (
    SELECT RecordNum, MAX(CAST(Date + ' ' + Time as DATETIME)) As MaxDateTime
      FROM MyTable
      GROUP BY RecordNum
  ) M2  ON M2.MaxDateTime = M.CAST(M.Date + ' ' + Time as DATETIME)
        AND M2.RecordNum = M.RecordNum
  
  ORDER BY RecordNum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources