Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jul 2004
    Posts
    48

    Unanswered: date select problem help..............

    hi all
    i've two tables containing date field
    i want to select one date from the first table and also the next highest date from the second table
    is there any provision for tht
    thnks in advance
    for eg

    tbl1 tbl2

    01/02/2004 15/01/2004

    05/01/2004 02/02/2004

    10/08/2004 03/02/2004

    i want to get like this

    01/02/2004 02/02/2004
    05/01/2004 15/01/2004

    jagans
    thnks in advance

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Assume, you have Tbl1.Dte1 and Tbl2.Dte2

    Then, your query should look like

    SELECT dte1 D1, (SELECT min(Dte2) from Tbl2 WHERE Dte2 > T1.Dte1) D2
    FROM Tbl1 T1

    Hope this helps
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jul 2004
    Posts
    48

    Smile thnks

    thnk u DoktorBlue thnk u very much

    i got it and it struggled me very much
    jagans

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should avoid nesting SELECT statements inside SELECT clauses. It can lead to inefficient execution plans. Try this:

    Code:
    select	tbl1.DateField as Date1,
    	min(tbl2.DateField) as Date2
    from	tbl1
    	left outer join tbl2 on tbl1.DateField < tbl2.DateField
    group by tbl1.DateField
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I wonder why you think that your query is more efficient than my solution. Can you explain?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    never mind the efficiency

    blindman's is correct, whereas yours, doktorblue, returns an extra unwanted row

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    ?

    I'm getting for each row of tbl1 one result row, so what is the extra row?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  8. #8
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    To remove result rows with no matching second date, the query should be changed to

    SELECT D1, D2 FROM (
    SELECT dte1 D1, (SELECT min(Dte2) from Tbl2 WHERE Dte2 > T1.Dte1) D2
    FROM Tbl1 T1) T
    WHERE D2 IS NOT NULL
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  9. #9
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Quote Originally Posted by r937
    never mind the efficiency

    blindman's is correct, whereas yours, doktorblue, returns an extra unwanted row

    Blindman's query returns exactly the same rows as my query, he would have to add a HAVING condition. So, what's your point?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by DoktorBlue
    I wonder why you think that your query is more efficient than my solution. Can you explain?
    Because you are literally telling the server to run the inner query 1 time for each row in the outer table. If the outer table contains 10,000 rows, the inner query could be executed 10,000 times. If all goes well, the query optimizer will convert your nested syntax into the more efficient JOIN syntax prior to execution, but if it is unable to do so then your query will run like a slug. Why not write it correctly to begin with?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and if you want to exclude rows from table1 where there is no higher date value in table2, then just change the LEFT OUTER JOIN to an INNER JOIN:

    Code:
    select	tbl1.DateField as Date1,
    	min(tbl2.DateField) as Date2
    from	tbl1
    	inner join tbl2 on tbl1.DateField < tbl2.DateField
    group by tbl1.DateField
    Simple.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    1) don't mix correctness and performance by insulting that my query is incorrect

    2) SQL Server isn't that stupid as you appearently think: it has concepts of indices and memory buffers

    3) Your solution will force SQl Server to create temp tables for sorting and grouping; WRITE operations are always slower than READs
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, sorry, inner join is obviously correct

    there are clearly 3 rows in table1 in the original post, and only 2 in the result
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    insulting? stupid?

    dude, you need to dial down your aggressiveness a notch or three

    your first solution was incorrect

    with all due respect

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Quote Originally Posted by r937
    insulting? stupid?

    dude, you need to dial down your aggressiveness a notch or three

    your first solution was incorrect

    with all due respect


    Ohh, I'm kalm as cold water. However, my query was as correct or incorrect as Blindman's. Moreover, didn't you notice that the poster already got the answer he asked for? There is no need at all to elaborate on (un)wanted rows.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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