I could use help for a sql query

[bnilsen]

Hello,
I'm working with SQL server 2005 and a database that has "equipment status" data that is spread across several tables and is time stamped in only one table, there are multiple instances of duplicate records with differing time stamps.

I need to select only the newest time stamped messages for each piece of equipment(a.resource), the criteria is: I need to return b.message_text, a.host, a.resource where a.message_num = b.message_num and the newest a.event_in_secs.

I’m new to SQL server (and databases, sql) and guess ‘cursor’ may be the way to go but I would appreciate any help. Thanks

[zmariow]

Do you know what are "INNER JOIN", "OUT JOIN" in SQL???

If no, then learn those concepts first and you'll be able to solve your problem (I think).

If you do, well... can you clarify your question a little more :)

Good luck

[hollystyles]

I’m new to SQL server (and databases, sql) and guess ‘cursor’ may be the way to go but I would appreciate any help. Thanks

Cursor? yuck! I have been programing SQL Server for 8 years and I have *never* used a cursor they are slooooowww and are completely contrary to set based theory. If you *have* to iterate data in a one by one scenario do it in code where it belongs not in the database. Or use a TSQL while loop or table variables.

You need to look into the GROUP BY clause of the SQL syntax.

Select
    b.message_text, a.host, a.resource,
    max(a.event_in_secs) as max_event_in_secs
from 
    a join b on a.message_num = b.message_num
group by 
     b.message_text, a.host, a.resource

[bnilsen]

Thanks for the help, I've only been at this a real short while - sorry if I didn't make sense, I only program in LabVIEW so I'm new to sql, but now I know the best path to pursue, I'm glad I didn't waste a bunch of time researching 'cursors'.

I'll try to clarify my task a little better..... I have a constant stream of data getting put into several different tables from thousands of different pieces of equipment throughout a very large satellite network. I need only 2 of the tables for my query, the first table (call it A) has 2 columns (message_text, message_num), the other (call it B) has several columns but I need only 4 of them (event_date, event_in_secs, message_num, resource).
I need to return the most current record for each piece of equipment (which is defined in B.resource) containing: A.message_text, B.event_date, B.resource.
The criteria is where A.message_num = B.message_num (this will return several dozen duplicates for each B.resource) and the latest (top?) B.event_in_secs for each instance of B.resource.

I hope this makes sense - thanks again (sucks to be a newbee)

[zmariow]

This query will help you get all the rows you need with duplicate entries and without selecting the most current record:

SELECT A.message_text, B.event_date, B.resource
FROM A INNER JOIN B
ON A.message_num = B.message_num

It's not your end query but it's a good start :)
Good luck.

[bnilsen]

Thanks for the tip, I'll try it when I get back to work.

[Samson_ZXL]

Hi guys!
I'm a new comer of this forum. I'm interested in sql statement :join. And can you show me the exact difference between inner join and left join ?

Thanks.

[hollystyles]

Samson, please don't 'hi-jack' threads. Here are the forum rules:

http://www.daniweb.com/forums/faq.php?faq=daniweb_policies

And here is a guide on how to post your own thread:

http://www.daniweb.com/forums/faq.php?faq=daniweb_howto

[mohanrobin]

The database is avast thing that connects several data and program related to that data.so
for your problem you have to study the inner join and outer join of the data base design.

[bnilsen]

Thanks,

Here's what I came up with - 5 lines and works great..........

select distinct atnm_log.resource, max(atnm_log.event_in_secs), message_table.message_text
from atnm_log, message_table
where atnm_log.message_num = message_table.message_num
and atnm_log.resource like '%par[0-3]%' or atnm_log.resource like '%par[12-13]%'
group by atnm_log.resource, message_table.message_text

Thanks for the help!