Professional Web Applications Themes

min( ) function. - Microsoft SQL / MS SQL Server

SELECT Code,MAX(Date) FROM TableA GROUP BY Code WHERE Date < GETDATE() -- Andrew J. Kelly SQL Server MVP "Ricardo" <ricardo.marquezenetspeculation.com> wrote in message news:043001c344b7$6c1400a0$a101280aphx.gbl... > Hi. > I have the following structure: > Table A: > code integer > sucode integer > date datetime > . . . > > Table B: > sucode integer > . . . > > from where I'd like to obtain, for each "sucode", the code > corresponding to the "nearest" (in time) code. By > the "nearest" one I mean the one from A whose "date" is > nearest to the current date. ...

  1. #1

    Default Re: min( ) function.

    SELECT Code,MAX(Date) FROM TableA GROUP BY Code WHERE Date < GETDATE()


    --

    Andrew J. Kelly
    SQL Server MVP


    "Ricardo" <ricardo.marquezenetspeculation.com> wrote in message
    news:043001c344b7$6c1400a0$a101280aphx.gbl...
    > Hi.
    > I have the following structure:
    > Table A:
    > code integer
    > sucode integer
    > date datetime
    > . . .
    >
    > Table B:
    > sucode integer
    > . . .
    >
    > from where I'd like to obtain, for each "sucode", the code
    > corresponding to the "nearest" (in time) code. By
    > the "nearest" one I mean the one from A whose "date" is
    > nearest to the current date.
    >
    > How can I write a SQL sentence that provides that output ?
    >
    > Thanks a lot in advance.
    >
    >
    >
    >

    Andrew J. Kelly Guest

  2. #2

    Default Re: min( ) function.

    SELECT b.sucode, (SELECT TOP 1 aa.date FROM tableA AA WHERE AA.sucode =
    b.sucode ORDER BY ABS(DATEDIFF(ms, aa.date, GETDATE())))
    FROM tableB b


    "Ricardo" <ricardo.marquezenetspeculation.com> wrote in message
    news:043001c344b7$6c1400a0$a101280aphx.gbl...
    > Hi.
    > I have the following structure:
    > Table A:
    > code integer
    > sucode integer
    > date datetime
    > . . .
    >
    > Table B:
    > sucode integer
    > . . .
    >
    > from where I'd like to obtain, for each "sucode", the code
    > corresponding to the "nearest" (in time) code. By
    > the "nearest" one I mean the one from A whose "date" is
    > nearest to the current date.
    >
    > How can I write a SQL sentence that provides that output ?
    >
    > Thanks a lot in advance.
    >
    >
    >
    >

    Jacco Schalkwijk Guest

  3. #3

    Default Re: min( ) function.

    Thanks a lot for your answer.
    However, when trying this I am getting and error message
    stating that there is a "syntax error near '1'". Any idea
    why that might be happening ?
    Perhaps my question hasn't been complete enough: I am
    using SQL Server 7.0

    >-----Original Message-----
    >SELECT b.sucode, (SELECT TOP 1 aa.date FROM tableA AA
    WHERE AA.sucode =
    >b.sucode ORDER BY ABS(DATEDIFF(ms, aa.date, GETDATE())))
    >FROM tableB b
    >
    >
    >"Ricardo" <ricardo.marquezenetspeculation.com> wrote in
    message
    >news:043001c344b7$6c1400a0$a101280aphx.gbl...
    >> Hi.
    >> I have the following structure:
    >> Table A:
    >> code integer
    >> sucode integer
    >> date datetime
    >> . . .
    >>
    >> Table B:
    >> sucode integer
    >> . . .
    >>
    >> from where I'd like to obtain, for each "sucode", the
    code
    >> corresponding to the "nearest" (in time) code. By
    >> the "nearest" one I mean the one from A whose "date" is
    >> nearest to the current date.
    >>
    >> How can I write a SQL sentence that provides that
    output ?
    >>
    >> Thanks a lot in advance.
    >>
    >>
    >>
    >>
    >
    >
    >.
    >
    Ricardo Guest

  4. #4

    Default Re: min( ) function.

    Ricardo,

    Whats the database compatibility level? It should be 70 and above for TOP to
    work You can check the compatibility level by
    using:

    sp_dbcmptlevel '<databasename>'

    To change it: sp_dbcmptlevel '<databasename>','70'

    Please refer BooksOnLine topic 'sp_dbcmptlevel' before changing the database
    compatibility level since it talks about the need to test the existing
    application code


    --
    Dinesh.
    SQL Server FAQ at
    [url]http://www.tkdinesh.com[/url]

    "Ricardo" <ricardo.marquezenetspeculation.com> wrote in message
    news:04a701c344c7$75db68c0$a301280aphx.gbl...
    > Thanks a lot for your answer.
    > However, when trying this I am getting and error message
    > stating that there is a "syntax error near '1'". Any idea
    > why that might be happening ?
    > Perhaps my question hasn't been complete enough: I am
    > using SQL Server 7.0
    >
    >
    > >-----Original Message-----
    > >SELECT b.sucode, (SELECT TOP 1 aa.date FROM tableA AA
    > WHERE AA.sucode =
    > >b.sucode ORDER BY ABS(DATEDIFF(ms, aa.date, GETDATE())))
    > >FROM tableB b
    > >
    > >
    > >"Ricardo" <ricardo.marquezenetspeculation.com> wrote in
    > message
    > >news:043001c344b7$6c1400a0$a101280aphx.gbl...
    > >> Hi.
    > >> I have the following structure:
    > >> Table A:
    > >> code integer
    > >> sucode integer
    > >> date datetime
    > >> . . .
    > >>
    > >> Table B:
    > >> sucode integer
    > >> . . .
    > >>
    > >> from where I'd like to obtain, for each "sucode", the
    > code
    > >> corresponding to the "nearest" (in time) code. By
    > >> the "nearest" one I mean the one from A whose "date" is
    > >> nearest to the current date.
    > >>
    > >> How can I write a SQL sentence that provides that
    > output ?
    > >>
    > >> Thanks a lot in advance.
    > >>
    > >>
    > >>
    > >>
    > >
    > >
    > >.
    > >

    Dinesh.T.K Guest

  5. #5

    Default Re: min( ) function.


    You're right. I tried this and the compatibility level seems to be
    '6.5'.

    Do I have any alternative to 'TOP n' in this level ?

    Thanks a lot for your answer.


    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Ricardo Marquez Guest

  6. #6

    Default Re: min( ) function.

    Ricardo,

    You should be able to rewrite Jacco's suggested solution
    in one of these ways without using TOP. You might also
    look into why the compatibility mode is set to 65 and see if
    that is necessary. I don't recommend just changing it to 70
    arbitrarily, though.

    SELECT b.sucode, (
    SELECT aa.date
    FROM tableA AA
    WHERE AA.sucode = b.sucode
    AND NOT EXISTS (
    SELECT * FROM tableA AA2
    WHERE AA2.sucode = b.sucode
    AND ABS(DATEDIFF(ms, aa.date, GETDATE())) > ABS(DATEDIFF(ms,AA2.date,
    GETDATE()))
    )
    )
    FROM tableB b

    or

    SELECT b.sucode, aa.date
    FROM tableB b, tableA AA
    WHERE AA.sucode = b.sucode
    AND NOT EXISTS (
    SELECT * FROM tableA AA2
    WHERE AA2.sucode = b.sucode
    AND ABS(DATEDIFF(ms, aa.date, GETDATE())) > ABS(DATEDIFF(ms,AA2.date,
    GETDATE()))
    )

    or

    SELECT b.sucode, aa.date
    FROM tableB b, tableA AA
    WHERE AA.sucode = b.sucode
    AND ABS(DATEDIFF(ms, aa.date, GETDATE())) = (
    SELECT MIN(ABS(DATEDIFF(ms, AA2.date, GETDATE())))
    FROM tableA AA2
    WHERE AA2.sucode = b.sucode
    )

    [untested queries]

    Steve Kass
    Drew University

    "Ricardo Marquez" <ricardo.marquezenetspeculation.com> wrote in message
    news:ODiETqMRDHA.1304TK2MSFTNGP11.phx.gbl...
    >
    > You're right. I tried this and the compatibility level seems to be
    > '6.5'.
    >
    > Do I have any alternative to 'TOP n' in this level ?
    >
    > Thanks a lot for your answer.
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Steve Kass Guest

Similar Threads

  1. Replies: 3
    Last Post: March 19th, 07:59 PM
  2. Replies: 0
    Last Post: October 24th, 12:01 AM
  3. Replies: 0
    Last Post: October 14th, 04:28 PM
  4. note 33575 added to function.register-shutdown-function
    By jules@sitepointAASASZZ.com in forum PHP Notes
    Replies: 0
    Last Post: July 1st, 05:41 AM
  5. Replies: 0
    Last Post: July 1st, 12:31 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139