Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2005
    Posts
    74

    Question Unanswered: Selecting the first record in a join

    What I am trying to do is hard to summarize in a few words, so forgive my vague subject.

    I have one table like this:
    Code:
    CREATE TABLE DlIndexTable
    (SessionStartTime DATETIME NOT NULL PRIMARY KEY, SchemaID INTEGER NOT NULL,
    DLBaseRate REAL NOT NULL)
    and one like so:
    Code:
    CREATE TABLE DlTextDataTable (
    SessionStartTime DATETIME NOT NULL REFERENCES DlIndexTable,
    ChTimestamp FLOAT NOT NULL, Channel01data VARCHAR (255),
    Channel02data VARCHAR (255), ... , Channel16data VARCHAR (255),
    CONSTRAINT TxtDatPriKey PRIMARY KEY (SessionStartTime, ChTimestamp))
    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:
    Code:
    SELECT DlIndexTable.SessionStartTime, DlTextDataTable.Channel01data
    FROM DlIndexTable
    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.

    Kamen
    Last edited by KamenG; 03-06-12 at 21:37.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You should really mention which version of SQL Server you are using...

    The following queries demonstrate the techniques you want to be looking at

    2005 and up
    Code:
    SELECT *
    FROM   your_table
     INNER
      JOIN (
            SELECT your
                 , fields
                 , Row_Number() OVER (PARTITION BY SessionStartTime ORDER BY ChTimestamp ASC) As row_num
            FROM   your_table
           ) As x
    WHERE  x.row_num = 1
    Earlier versions
    Code:
    SELECT *
    FROM   (
            SELECT SessionStartTime
                 , Min(ChTimestamp) As minCHTimestamp
            FROM   your_table
           ) As x
        ON x.SessionStartTime = your_table.SessionStartTime
       AND x.minCHTimestamp = your_table.CHTimestamp
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2012
    Posts
    8
    select Top1 then followed by the Query u can use just try it

  4. #4
    Join Date
    Dec 2005
    Posts
    74
    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.
    Kamen

  5. #5
    Join Date
    Dec 2005
    Posts
    74
    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:
    Code:
    SELECT * FROM DlIndexTable
    INNER JOIN
    (
    	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.
    Kamen
    Last edited by KamenG; 03-02-12 at 21:03.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "Incorrect syntax near the keyword 'WHERE'" is because your ON clause for the INNER JOIN is missing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2005
    Posts
    74
    Thanks, but where should I put it? I really don't understand the query that George posted, and it doesn't have an "ON" anywhere in it?
    Kamen

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should put it before the WHERE clause

    george's 2nd query has an ON clause, his first is deficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2005
    Posts
    74
    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.
    Kamen
    Last edited by KamenG; 03-06-12 at 21:19.

  10. #10
    Join Date
    Dec 2005
    Posts
    74

    Lightbulb Solved

    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):
    Code:
    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.
    Kamen

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    (Not tested.)
    Code:
    SELECT ix.SessionStartTime
         , td.Channel01data
     FROM  DlIndexTable AS ix
     LEFT  JOIN
          (SELECT SessionStartTime
                , Channel01data
                , ROW_NUMBER()
                     OVER( PARTITION BY SessionStartTime
                               ORDER BY ChTimestamp ASC
                         ) AS row_num
            FROM  DlTextDataTable
          ) AS td
      ON   ix.SessionStartTime = td.SessionStartTime
       AND td.row_num = 1
     WHERE ix.SessionStartTime
           BETWEEN '2006-10-13 16:40:08.790'
               AND '2012-03-01 17:54:30.930'
     ORDER BY
           ix.SessionStartTime
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •