Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: getting sorted result set

    I have very simple stored procedure
    CREATE PROCEDURE superdb560.strpoDefault6liVitrin AS

    SELECT intMarkaID, intModelID, strModelAdi, strMarkaAdi
    FROM x

    WHERE
    (intModelID = 4) OR
    (intModelID = 146) OR
    (intModelID = 157) OR
    (intModelID = 232) OR
    (intModelID = 282) OR
    (intModelID = 150)

    GO

    When I run this query I get rows sorted according to intModelID

    intModelID
    2 4 3510 Nokia
    2 146 6100 Nokia
    4 150 N500 Samsung
    2 157 3650 Nokia
    3 232 GD87 Panasonic
    2 282 3300 Nokia

    But I want to get the rows as I write in the where clause . Like 4, then 146, then 157 then 232... then 150

  2. #2
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    I'm not sure what you store in your intmodelid,

    but if its just numbers, I think after the where clause you can add in an "order by intmodelid".

    Haven't test the code, but I think that is the way. Unless intmodelid is not only numbers.

    woops..wait...sorry...didn't see u want it sorted in that order...this is not the solution...
    Last edited by Patrick Chua; 08-25-03 at 14:22.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  3. #3
    Join Date
    Aug 2003
    Posts
    13

    Re: getting sorted result set

    Originally posted by sahin boydas
    I have very simple stored procedure
    CREATE PROCEDURE superdb560.strpoDefault6liVitrin AS

    SELECT intMarkaID, intModelID, strModelAdi, strMarkaAdi
    FROM x

    WHERE
    (intModelID = 4) OR
    (intModelID = 146) OR
    (intModelID = 157) OR
    (intModelID = 232) OR
    (intModelID = 282) OR
    (intModelID = 150)

    GO

    When I run this query I get rows sorted according to intModelID

    intModelID
    2 4 3510 Nokia
    2 146 6100 Nokia
    4 150 N500 Samsung
    2 157 3650 Nokia
    3 232 GD87 Panasonic
    2 282 3300 Nokia

    But I want to get the rows as I write in the where clause . Like 4, then 146, then 157 then 232... then 150
    Use a CASE in your ORDER BY clause. Like this:

    order by
    case
    when intModelID = 4 then 1
    when intModelID = 146 then 2
    when intModelID = 157 then 3
    when intModelID = 232 then 4
    when intModelID = 150 then 5
    end

  4. #4
    Join Date
    Aug 2003
    Posts
    2

    not efficient

    Mr. DrummerCA33

    the solution tahat you suggest will solve the problem, but It will not be efficient. Think that I have table that have 1000 thousand rows.Then We can not write 1000 cases. There should be more efficient way.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    create another table with intModelID and RankID (for example) and assign the rank to each model id. Then when you do your select you can join it by intModelID and order by RankID.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    sahin,

    Complete this series: 4, 146, 157, 232, 282, 150, ....

    I don't think a cadre' of Mensa members on Ginko Biloba could figure this out. If there is some logic behind this sort order (date entered, product price, whatever), then that value either IS in the database, or you need to create it and add it to the database. Then you sort on it.


    blindman

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Ranking values is a very common task, that's why sahin needs this other table to not only resolve the sort order but also to retain the fact of ranking. ...and what are you talking about, blind man?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    rdjabarov

    This link may help: www.dictionary.com

    blindman

  9. #9
    Join Date
    Aug 2003
    Posts
    13

    Re: not efficient

    Originally posted by sahin boydas
    Mr. DrummerCA33

    the solution tahat you suggest will solve the problem, but It will not be efficient. Think that I have table that have 1000 thousand rows.Then We can not write 1000 cases. There should be more efficient way.
    A "thank you" would be nice instead of a "not efficient", but hey, what can I expect. In any event, you only have 6 values, hard coded in your sproc, so I fail to see why a case statement wouldn't work.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    bm: put this link in your favorites. May help

    How to be polite?

Posting Permissions

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