bkourouma
asked on
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
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
SQL_SERVER_DBA
use cursor and then pivot the results
ASKER
I am trying to avoid using cursors. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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;
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
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
ASKER
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.
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.