tricky SQL?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: tricky SQL?

Hybrid View

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    Need little help here, trying to write an SQL that do as follows

    have two tables and when I join them I would get something like

    Column A---Column B
    New York----David
    New York----John
    New York----Alex
    Atlanta-------Chris
    Florida-------Phil
    L.A-----------David
    L.A-----------Jenny
    L.A-----------Boris
    L.A-----------Joseph
    Miami--------Alex
    Miami--------Will

    Now I want the query to return just a limited rows using rownum, for example

    where rownum <= 3

    However this would return

    Column A---Column B
    New York----David
    New York----John
    New York----Alex

    ONLY

    But I need all results of first column <= 3, what i am saying is that I need the query to return me

    Column A---Column B
    New York----David
    New York----John
    New York----Alex
    Atlanta-------Chris
    Florida-------Phil

    where column A has 3 values

    is this possible at all? I have seek a few ways but cant do it!!!

    Cheers

  2. #2
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    Select Column_B from Table
    where Column_A in (select distinct Column_A from Table where rownum<=3);

    Sergey.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Pando,

    If you are using 8.1.6 or above you can take advantage of analytycal functins. In your case you would use new ROW_NUMBER() function:

    SELECT column_a, column_b FROM your_table
    WHERE (ROW_NUMBER() OVER (PARTITION BY column_a)) <= 3;

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    sorry I forgot to say that the result set is from a join between two tables


    btw I am using 8.1.6.3 on Solaris 2.6

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I don't have any database handy to test my SQLs, but I just looked at SQL manual and found out that you can only use analytical functions in the SELECT LIST and in ORDER BY clause of a query, not in the WHERE clause. So you have to use inline views. For joining two tables and showing only up to 3 names for each city, you would use:

    SELECT colA, colB FROM
    (SELECT t1.colA, t2.colB, ROW_NUMBER() OVER (PARTITION BY column_a) row_num_for_colA
    FROM t1, t2 WHERE .....)
    WHERE row_num_for_colA <= 3;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Is that partition compulsory...? The tables I am querying arent partitioned... I will have a look at documentation as well, did you find this in the SQL guide?

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    In analytical functions, PARTITION BY have nothing to do with table partitions (or index partitions). It merely defines on what range the analytical function is aplied. In this case, a distinct city name reperesents a partition and on each of this partitions each rows gets sequential row number (starting with 1 at each new "partition"). And yes, these new analytical functions are explained in SQL manual (from 8.1.6 onwards).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hm tried this and it wouldnt work, I am getting a result set like this


    Column A---Column B---ROW_NUM_FOR_COL_A
    New York----David--------1
    New York----John---------2
    New York----Alex----------3
    Atlanta-------Chris---------1
    Florida-------Phil------------1
    L.A-----------David---------1
    L.A-----------Jenny---------2
    L.A-----------Boris----------3
    Miami--------Alex------------1
    Miami--------Will--------------2

    Basically itīs filtering on the last column, and I want to *ASSIGN* row number 1 to New York, 2 to Atlanta, 3 to Florida etc...

    :(

  9. #9
    Join Date
    Feb 2000
    Posts
    175
    Hi,

    I'm not sure if this would help in your quest for the answer but

    If you create a temporary table of distinct values of columnA then use this in your select with the rownum <=3.

    eg

    create table temp_tab as (select distinct(columnA) from t1;

    select t1.columnA, t2.columnB
    from t1, t2
    where t1.columnA in
    (select columnA from temp_tab where rownum <=3)
    and
    ......

    In your example it will return the following

    Atlanta ----Chris
    Florida ----Phil
    L.A -----Boris
    L.A -----David
    L.A -----Jenny
    L.A -----Joseph

    (It sorts it into order when you create the temp table using select distinct)

    Hope this helps.

    If not I'd be interested to see how the problem is solved. I'd appreciate it if you could E-Mail the solution to cmoffat99@yahoo.co.uk - It's got me a little bugged on how you can get the exact result you asked for - unsorted.

    Kind Regards

    Moff

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    No that wouldnt work, the example I posted is a simplified version what we are trying to do in a search engine (thatīs why I have to limit the result set using rownum) If I start to create temporary tables it would be one temporary table for each search since the conditions in the search can vary quite a lot...



    [Edited by pando on 03-06-2001 at 07:08 AM]

Posting Permissions

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



Click Here to Expand Forum to Full Width