Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Posts
    76

    Unanswered: first 2 rows from table

    How do I get the first 2 rows from a table based on the value of a specific column?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Which RDBMS?
    Code:
    -- SQL Server:
    select top 2 * from mytable where colx='Value';
    -- Oracle
    select * from mytable where colx='Value' and rownum <=2;
    PS: Also, it depends on how you define first two rows!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by LKBrwn_DBA
    Also, it depends on how you define first two rows!
    it was stated: based on the value of a specific column

    what wasn't stated was whether this means the lowest 2 or the highest 2

    your two example don't work, LKBrwn_DBA, because they don't have an ORDER BY

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    ...or any two with a specific value for the column.

    LKBrwn_dba solved one problem, Rudy solved a different problem. From the specs, I can't tell which of the two problems Naweed meant, either one could work for me.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    That is precisely why I stated:
    it depends on how you define first two rows!
    Meaning: which are the requirements? ordered/non-ordered,..etc
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Nov 2003
    Posts
    76

    Question first 2 rows from table

    SELECT sku, price FROM sku
    WHERE price IS NOT NULL
    AND ROWNUM <= '4'
    ORDER BY price DESC

    I tried this to get the 4 highest price sku records. I am not getting the correct result. What am I doing wrong? This is on Oracle. the datatype for price is NUMBER

  7. #7
    Join Date
    Nov 2003
    Posts
    76

    Question first 2 rows from table

    SELECT sku, price FROM sku
    WHERE price IS NOT NULL
    GROUP BY sku, price
    ORDER BY MAX(price) DESC

    this gets me sku in desc price order. Now I need to get the top 2. I tried the rownum <= 2 but it didnt quite work.

  8. #8
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    just putting rownum condition will not work because the conditions are tested at each row, not after the rows are already collected in a cursor. The following should work:

    Code:
    select rownum as "rank", price, sku
    from (select price, sku from sku where price is not null order by price desc)
    where rownum<=2
    You have to get a collection of what you want and then from that collection weed out data based on rownum
    Last edited by da_coolestofall; 02-24-05 at 18:12.

  9. #9
    Join Date
    Nov 2003
    Posts
    76

    Unhappy

    yes, this works, however I still dont understand how.

  10. #10
    Join Date
    Mar 2004
    Location
    California
    Posts
    58

    explanation

    ok here we go

    Step 1. the system creates a cursor from the inner select which had the rowid, rownum and the columns you selected in the right order.

    Step 2: from this collection you are selecting those rows only where the row number is less than equal to 2

    To understand why your approach does not work, you need to realize that when you attach a condition in the where clause, the system evaluates that clause with every row it looks at. So in your case the system goes to every row in the sku table and asks is row num less than 2. Since you are only comparing one row at a time, at any given instance the row num of that row is 1, hence the condition is satisfied and all rows are returned.

    In my approach, I first create a cursor based on all my other conditions, and then use the rownumbers on this cursor to get info I need.

    TO ifnd out a little more read about "Top-N" analysis in any Oracle SQL book.

  11. #11
    Join Date
    Nov 2003
    Posts
    76

    Smile first 2 rows in the table

    Thank you very much. Tell me where I can find your book. Is it available on borders or barnes and nobles?

  12. #12
    Join Date
    Jan 2004
    Posts
    492
    You can read all you want about SQL right here...for FREE!!

    http://download-west.oracle.com/docs...a96540/toc.htm
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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