SQL select statement

Posted on 2007-10-10
Last Modified: 2013-12-07
Hi Experts,

I have the following table


id              Imge_url               fk_Userid
=======  ==========         =============
1              22.jpg                   47
1              23.jpg                   47
1              24.jpg                   47
1              28.jpg                   48
1              29.jpg                   48
1              30.jpg                   47
1              31.jpg                   49
1              32.jpg                   50

What I need to do is return the top 2 images for each user as follows:

Imge1_url            Imge2_url            fk_Userid
=======           ==========         =============
22.jpg               23.jpg                   47
28.jpg               29.jpg                   48
31.jpg               NULL                    49
32.jpg               NULL                    50

I am using a cursor to do this but I read of performance issues with cursors.


Question by:bkourouma
    LVL 16

    Expert Comment

    use cursor and then pivot the results
    LVL 1

    Author Comment

    I am trying to avoid using cursors. Thanks
    LVL 22

    Accepted Solution

    It would help to know what product and version you are using. The following is standard SQL 2003, tested on SQL Server 2005:

    SELECT MIN(Imge_url) AS Imge1_url,
     MIN(CASE WHEN rn = 2 THEN Imge_url END) AS Imge2_url,
     (PARTITION BY fk_Userid ORDER BY Imge_url) rn,
      Imge_url, fk_Userid FROM tbl_PHOTOS) t
     WHERE rn <=2
     GROUP BY fk_Userid;
    LVL 8

    Expert Comment

    by:Bradley Haynes
    Change the N from 10 to 2.

            SELECT *
            FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)
            WHERE  ROWNUM < 10;

    Use this workaround with prior releases:

            SELECT *
              FROM my_table a
             WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
                            FROM my_table b
                           WHERE b.maxcol >= a.maxcol)
             ORDER BY maxcol DESC;
    LVL 8

    Expert Comment

    by:Bradley Haynes
    Returning only the first N records in a SQL query differs quite a bit between database platforms. Here's some samples:

    Microsoft SQL Server

    SELECT TOP 10 column FROM table

    PostgreSQL and MySQL

    SELECT column FROM table
    LIMIT 10


    SELECT column FROM table
    WHERE ROWNUM <= 10


    SET rowcount 10
    SELECT column FROM table


    SELECT FIRST 10 column
    FROM table

    Due to these differences if you want to keep your code database independent you should use the maxrows attribute in the cfquery tag in ColdFusion. The tradeoffs to database independance is performance, I would expect maxrows to be slower than specifying the rows in the SQL.

    <cfquery datasource="#ds#" maxrows="10">
    SELECT column FROM table

    PostgreSQL and MySQL have a cool feature that will let you return an arbitrary range of rows (eg return rows 10-20). This is very handy for displaying pages of records:

    SELECT column FROM table
    LIMIT 10 OFFSET 20

    The above query will return rows 20-30
    LVL 1

    Author Comment

    I am using SQL 2005


    I get the following error:

    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    Excel to create SQL insert 7 28
    Pfile and SPfile - Oracle 2 19
    report c# 9 56
    Which one to download? 8 29
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now