Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2008
    Posts
    4

    Red face Unanswered: Subquery problem

    Hi friends,

    I have written a query I am having error
    First I should show my table structure , There's only one table used in this query.
    Here I have stored Kilometer reading of each truck on each trip..... Like

    TruckID KMReading
    1 332
    5 150
    4 950
    1 440
    1 520
    4 1200


    And now I want to show the report in by trip kilometers... Like First trip took (440-332)=108 Kms second trip took 520-440=80 Kms for truckid =1

    TrID KMR TotKM
    1 332 0
    1 440 108
    1 520 80


    I have written select statement and almost it is working but I dont know somewhere I returns more than one records in sub query even I have written top 1 ... please check this out and help me

    select a.*, (select top 1 b.KMReading from ExpenceEntry b where b.KMReading > a.KMReading and b.TruckId=a.truckID order by b.KMReading asc) -a.KMReading as KRead from expenceEntry a
    order by a.EntryDate,a.TruckID, a.KMReading

    It gives error "at most one record can be returned by this subquery" but if I remove Order by in subquery it does not give error but gives wrong output

    Somebody please help.... I have already took many days to solve this...
    Thank you in advance...

  2. #2
    Join Date
    Feb 2008
    Posts
    4

    Subquery problem

    Hi friends,

    I have written a query I am having error
    First I should show my table structure , There's only one table used in this query.
    Here I have stored Kilometer reading of each truck on each trip..... Like

    TruckID KMReading
    1 332
    5 150
    4 950
    1 440
    1 520
    4 1200


    And now I want to show the report in by trip kilometers... Like First trip took (440-332)=108 Kms second trip took 520-440=80 Kms for truckid =1

    TrID KMR TotKM
    1 332 0
    1 440 108
    1 520 80


    I have written select statement and almost it is working but I dont know somewhere I returns more than one records in sub query even I have written top 1 ... please check this out and help me

    select a.*, (select top 1 b.KMReading from ExpenceEntry b where b.KMReading > a.KMReading and b.TruckId=a.truckID order by b.KMReading asc) -a.KMReading as KRead from expenceEntry a
    order by a.EntryDate,a.TruckID, a.KMReading

    It gives error "at most one record can be returned by this subquery" but if I remove Order by in subquery it does not give error but gives wrong output

    (I have tried this in MsAccess)

    Somebody please help.... I have already took many days to solve this...
    Thank you in advance...

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Have a look at the GROUP BY syntax.
    Have a nice day!

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Your problem/solution is similar to this.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    have a look at this reply
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Threads merged since this is T-SQL syntax
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

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