Results 1 to 11 of 11
  1. #1
    Join Date
    May 2007
    Posts
    2

    Question Unanswered: Need Help on SQL statement

    Hi everyone , I have a table as follow


    CustID Name ComplainDate Status
    10001 | Nina | 10-Jun-06 | Accept
    10001 | Nina | 11-Jun-06 | Accept
    10001 | Nina | 13-Jun-06 | Reject
    10002 | John | 12-Jan-00 | Reject
    10002 | John | 13-Jan-00 | Accept
    10003 | Daniel | 18-Feb-00 | Reject

    but when I create a query view I need to appear with the ranking column but I no idea how to write the SQL statement which output of the view as follow:

    CustID Name ComplainDate Status Rank
    10001 | Nina | 10-Jun-06 | Accept | 1
    10001 | Nina | 11-Jun-06 | Accept | 2
    10001 | Nina | 13-Jun-06 | Reject | 3
    10002 | John | 12-Jan-00 | Reject | 1
    10002 | John | 13-Jan-00 | Accept | 2
    10003 | Daniel | 18-Feb-00 | Reject | 1


    Anyone pls help thanks in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is the general method. Things get a little more complex if CustID and ComplainDate do not represent a unique key.
    Code:
    Select	[YourTable].CustID,
    	[YourTable].Name,
    	[YourTable].ComplainDate,
    	[YourTable].Status,
    	count(*) as Rank
    from	[YourTable]
    	inner join [YourTable] Ordinal
    		on [YourTable].CustID = Ordinal.CustID
    		and [YourTable].ComplainDate >= Ordinal.ComplainDate
    group by [YourTable].CustID,
    	[YourTable].Name,
    	[YourTable].ComplainDate,
    	[YourTable].Status
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    May 2007
    Posts
    2
    thanks for your solution man

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    There is a better way to do this without using the count() function that SQL provides. Imagine if you had to do a count for 10,000 rows
    It requires that you use an incrementing variable integer.
    Give me a day and i'm sure I can work it out... it's a tricky process..

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What's wrong with the COUNT? The system has to process all rows anyway. With a suitable execution plan, it will process rows by group already and then the count is trivially computed along the way.

    Another alternative is to use the SQL RANK or DENSE_RANK window functions.
    Last edited by stolze; 05-08-07 at 12:40.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    aschk, if he just wanted an ordinal value across the entire dataset he could select it into a temporary table with an auto-incrementing Identity. I think this is what you are thinking of. But the poster wants ordinal values across groups within the data, so that technique won't work.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Right, using GENERATED ALWAYS/DEFAULT identity columns or sequences would be applicable to the whole table. But there is another drawback for identity columns: you would have to materialize the temp data. Subselects also produce (logical) temp tables, but no materialization is necessary.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    As an example of what i was going to demonstrate :

    Code:
    SELECT CustID,Name,ComplainDate,Status
          ,IF(@custno = CustID
          ,@rank := @rank + 1
          ,@rank := 1 + least(0,@custno := CustID)
         ) Rank
    FROM test AS t, (select (@custno:=0),(@rank:=0)) x
    ORDER BY CustID
    No count
    Last edited by aschk; 05-09-07 at 10:08.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Besides, the above syntax not being valid SQL, you have another major problem: That won't work at all because it assumes that the ORDER BY is executed before the SELECT list. But SQL states that first the SELECT list is evaluated and then any ORDER BY applied. Thus, the rows may come in any order first, where the rank thing is computed. Then the whole thing is sorted by CustID. Obviously, you have no guarantee for specific Rank values.

    So your only options (with standard SQL) are COUNT, RANK/DENSE_RANK, ROW_NUMBER, or common table expressions.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Apologies, I always seem to find myself in the SQL forum when I should be in the MySQL forum
    MySQL doesn't have the RANK/DENSE_RANK/OVER PARTITION options that other DBS exhibit. Hence the solution offered.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This being a general "SQL" forum, your answer was perfectly appropriate, but if it uses syntax specific to MySQL you should note that.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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