• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

SQL select statement

Hi Experts,

I have the following table

tbl_PHOTOS

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.

Thanks



0
bkourouma
Asked:
bkourouma
1 Solution
 
SQL_SERVER_DBACommented:
use cursor and then pivot the results
0
 
bkouroumaAuthor Commented:
I am trying to avoid using cursors. Thanks
0
 
dportasCommented:
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,
 fk_Userid
 FROM (SELECT ROW_NUMBER() OVER
 (PARTITION BY fk_Userid ORDER BY Imge_url) rn,
  Imge_url, fk_Userid FROM tbl_PHOTOS) t
 WHERE rn <=2
 GROUP BY fk_Userid;
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Bradley HaynesCommented:
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;
0
 
Bradley HaynesCommented:
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

Oracle

SELECT column FROM table
WHERE ROWNUM <= 10

Sybase

SET rowcount 10
SELECT column FROM table

Firebird

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
</cfquery>

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
0
 
bkouroumaAuthor Commented:
I am using SQL 2005


b_haynes:

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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now