I want to get some combined data from both tables, so right now I am joining them at the SessionStartTime column, which is a primary key in the first and a foreign key in the second table, something like this:
SELECT DlIndexTable.SessionStartTime, DlTextDataTable.Channel01data
LEFT JOIN DlTextDataTable
ON DlIndexTable.SessionStartTime = DlTextDataTable.SessionStartTime
WHERE DlIndexTable.SessionStartTime BETWEEN '2006-10-13 16:40:08.790' AND '2012-03-01 17:54:30.930'
ORDER BY DlIndexTable.SessionStartTime, DlTextDataTable.ChTimestamp
The trouble is that this query, exactly as requested, gives me all the entries from the second table matching the first, while I really would like to pick just one row (preferably, the first chronologically - by ChTimestamp) so that the first column (SessionStartTime) has distinct entries in the resulting table. What would be the simplest way of doing that? Performance is not a big priority over simplicity since the first table could have only a few hundred rows (maybe a couple of thousand), while the second will be real tiny. I'd appreciate your input.
Thank you for the responses, guys. And sorry for the delay - I was in a meeting all day today. I'll need some time to understand and implement the proposed solution. I was also hoping to get a more ANSI solution (I know I posted in the MS SQL forum, but I've had the best luck in this forum before). I am using a 2005 or newer SQL server, but I always try to stay as compatible as possible, in case we ever switch to something else. I'll keep you posted.
I was able to take some time and look at the suggested query, although, I have not had time yet to analyze it and understand it. As I've tried it, it doesn't execute (gives me a "Incorrect syntax near the keyword 'WHERE' message). Here's one variation:
SELECT * FROM DlIndexTable
SELECT SessionStartTime, Channel01data, Row_Number() OVER
(PARTITION BY SessionStartTime ORDER BY ChTimestamp ASC) AS row_num FROM DlTextDataTable
) AS x
WHERE x.row_num = 1
But all permutations give me the same error.
I even tried pasting George's example as it was, and still got the same error. I don't know that that's the problem but the one thing that confuses me the most is which table is "your table" in the example above, since I have two tables that I'm getting data from (and trying to join). I should mention also that the relationship is one-to-many, i.e., the DlIndexTable has more records than the DlTextDataTable, and all records in the latter match a SessionStartTime in the former.
I guess we can pick this up on Monday.
I am really sorry, but I can't figure this out, and none of the examples work, even after taking some guesses as to which table (DlIndexTable or DlTextDataTable) is "your_table" supposed to be. I've been studying Row_Number() and OVER PARTITION BY but I'm not getting it yet.
I should probably mention that my DlTextDataTable.Channel01data column can accept NULL data, and I still need to be showing those, which is why I was surprised to see an INNER joins suggested, but even after replacing it with a LEFT JOIN (as in my example) it still is not showing me all records I need. I need all records from DlIndexTable to be matched, so the result should have the exact same number of rows as DlIndexTable. My current query (as shown in my first post) shows more records than that.
I'd appreciate some help.
Never mind, I figured it out (it's probably not optimal but at least I hope it's not wrong). Here's the query that ended up working (and I was able to stay with ANSI SQL):
SELECT DlIndexTable.SessionStartTime, Y.Channel01data FROM
(SELECT X.SessionStartTime, DlTextDataTable.Channel01data FROM
(SELECT SessionStartTime, MIN(ChTimestamp) AS MinChTS FROM DlTextDataTable GROUP BY SessionStartTime) AS X
INNER JOIN DlTextDataTable
ON X.SessionStartTime = DlTextDataTable.SessionStartTime AND X.MinChTS = DlTextDataTable.ChTimestamp) AS Y
RIGHT JOIN DlIndexTable
ON y.SessionStartTime = DlIndexTable.SessionStartTime
If anyone sees a way to improve it, I'd gladly entertain all options.
Thanks for the guidance.
FROM DlIndexTable AS ix
OVER( PARTITION BY SessionStartTime
ORDER BY ChTimestamp ASC
) AS row_num
) AS td
ON ix.SessionStartTime = td.SessionStartTime
AND td.row_num = 1
BETWEEN '2006-10-13 16:40:08.790'
AND '2012-03-01 17:54:30.930'