dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: sql statement

  1. #1
    Join Date
    Mar 2003
    Posts
    62

    Question sql statement

    Hi, i've got a question using SQL. I have a table which has following attributes: beginn, end, ressource. all are numbers.
    i need a statement to select all records on one ressource that have the latest end of all records on the same ressource. anyone a suggestion? do i need a join or something?





    thx in forward,

    LaxRoth

  2. #2
    Join Date
    May 2002
    Location
    Colombo,Sri Lanka
    Posts
    1,110
    Try This


    Select * from [Table] Where UnitPrice In ( Select Max(End) from [Table] ) And ressource = 1

  3. #3
    Join Date
    Mar 2003
    Posts
    62
    thx, but the problem is that i haven't got the ressource =... i need all records!

  4. #4
    Join Date
    May 2002
    Location
    Colombo,Sri Lanka
    Posts
    1,110
    Can u put some data here and the output you are expecting

  5. #5
    Join Date
    Mar 2003
    Posts
    62
    hello, here is some data.

    pk ressource end
    == ======= ===
    1 1000 20040501_000000
    2 1000 20040503_000000
    3 1000 20040617_083200
    4 1005 20030101_105055
    5 1005 20030823_000000
    6 1005 20040505_000000
    7 1006 20031201_080000
    8 1008 20021231_000000
    9 1008 20040825_123746
    10 1008 20040826_080000

    my date of which i want the last record per ressource before is: 20040502_000000

    the records in the table are ordered randomly.

    date format is: yyyymmdd_hhmmss, so i can select with 'order by end' to have it in a timeline.

    i want to select the following records:
    1, 5, 7, 8

    u see i want to every ressource in the table the corresponding record with the backward nearest date to 20040502_000000.

    thx in advance, LaxRoth




    Last edited by LaxRoth; June 23rd, 2004 at 01:51 AM.

  6. #6
    Join Date
    Oct 2003
    Location
    Italy
    Posts
    42

    Smile

    Hi LaxRoth, I don't know if you can do with only sql language what you want but I'll write you this statement that permit to select the max records with the same "ressource".

    Select * from [Table] WHERE [End] IN (Select Max([End]) from [Table] GROUP BY ressource)

    I hope that you can use it.
    Bye

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)