filter data - SQL

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jkshetty
    New Member
    • Oct 2007
    • 6

    #1

    filter data - SQL

    Table :

    Name Month Marks

    A Sept 150
    B Sept 200
    C Sept 100
    D Sept 500

    A Oct 100
    B Oct 225
    C Oct 300
    D Oct 115



    Query
    -----
    Select only those students where students score more marks than previous month.
    like improving students




    REsults should be
    -------
    B OCT 225
    C Oct 300
  • Jim Doherty
    Recognized Expert Contributor
    • Aug 2007
    • 897

    #2
    Originally posted by jkshetty
    Table :

    Name Month Marks

    A Sept 150
    B Sept 200
    C Sept 100
    D Sept 500

    A Oct 100
    B Oct 225
    C Oct 300
    D Oct 115



    Query
    -----
    Select only those students where students score more marks than previous month.
    like improving students




    REsults should be
    -------
    B OCT 225
    C Oct 300

    What have you tried in place yourself to answer this?
    The SQL (see below) to display the result as you have there will be flawed in a real sense. Can you see why?

    Code:
     
    SELECT	 TOP 100 PERCENT A.Name, A.[Month], A.Marks
    FROM		 dbo.tblMarks A INNER JOIN
    					 dbo.tblMarks B ON A.Name = B.Name AND A.Marks > B.Marks AND B.[Month] > A.[Month]
    Jim :)

    Comment

    Working...