2009年7月29日 · UPDATE TOP (1) table1 SET column1 = 0 WHERE column_pk = '123' Without the TOP clause, if you are doing a manual update and your mouse text selection only selects from "UPDATE" to just before the "WHERE" clause, then the update is applied to ALL rows. With the TOP clause, only one row would get the undesired update.

With release 12.1, Oracle introduced "real" Top-N queries. Using the new FETCH FIRST... syntax, you can also use: SELECT * FROM ( SELECT id, client_id, create_time, ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn FROM order ) WHERE rn = 1 ORDER BY create_time desc FETCH FIRST 100 ROWS ONLY)

Here is a solution which will return more than 10 rows if there are ties but you will get all the rows where some_value_column is technically in the top 10. select * from (select *, rank() over (order by some_value_column desc) as my_rank from mytable) subquery where my_rank <= 10

2010年3月23日 · You get an apparently random set because ROWNUM is applied before the ORDER BY. So your query takes the first ten rows and sorts them.0 To select the top ten salaries you should use an analytic function in a subquery, then filter that:

2008年10月7日 · Database is SQL Server 2005. I want to return the top 10 by date entered. Sections are business, local, and feature. For one particular date I want only the top (10) business rows (most recent entry), the top (10) local rows, and the top (10) features.

2015年4月16日 · The ANSI SQL answer is FETCH FIRST.. SELECT a.names, COUNT(b.post_title) AS num FROM wp_celebnames a JOIN wp_posts b ON INSTR(b.post_title, a.names) > 0 WHERE b.post_date > DATE_SUB(CURDATE(), INTERVAL 1 DAY) GROUP BY a.names ORDER BY num DESC FETCH FIRST 10 ROWS ONLY

2009年12月18日 · SQL Server 2005 actually allows us to parameterize the TOP clause, using a variable, expression or statement. So you can do things like: SELECT TOP (@foo) a FROM table ORDER BY a SELECT TOP (SELECT COUNT(*) FROM somewhere else) a FROM table ORDER BY a SELECT TOP (@foo + 5 * 4 / 2) a FROM table ORDER BY a Source

I see this is an old question, but I can suggest a more elegant solution:. SELECT personDetails.* FROM ( SELECT TOP (10) personID personID FROM TableA GROUP BY personID --> use this instead of distinct ) AS distinctIds OUTER APPLY ( SELECT TOP (1) * --> fetch only one result matching personID FROM TableA WHERE TableA.personId = …

2012年3月30日 · I have to select the top 25 records from a table according to the column Num. There are two issues. First, the table is not sorted by Num. I know this can be resolved by using GROUP ORDER BY. Second, the number of the records in the table might be less than 25. Is there any way to perform this selection in just one SQL statement?

2010年1月9日 · Use OUTER APPLY instead of LEFT JOIN: SELECT u.id, mbg.marker_value FROM dps_user u OUTER APPLY (SELECT TOP 1 m.marker_value, um.profile_id FROM dps_usr_markers um (NOLOCK) INNER JOIN dps_markers m (NOLOCK) ON m.marker_id= um.marker_id AND m.marker_key = 'moneyBackGuaranteeLength' WHERE um.profile_id=u.id ORDER BY m.creation_date ) AS MBG WHERE u.id = 'u162231993';