Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I wrote a query below which shoots me a syntax error why would it do so,

SELECT MAX('Row') FROM 

(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS)

Error Desc:

Incorrect syntax near ')'.

I don't get it :(

share|improve this question

4 Answers 4

up vote 11 down vote accepted

There are actually two problems with the query. The first is Max('Row') will return the string 'Row'. The Second is your subquery needs an alias.

Try like this:

 SELECT MAX(Row) FROM 
 (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) Row FROM USERS) UserQuery

UPDATE:

I guess there are actually 3 problems with this query :). The 3rd being, count() is a much better way as expertly described by @gbn.

share|improve this answer
2  
Shouldn't the column alias be put into double quotes? Single quotes denote a string literal... –  a_horse_with_no_name Jul 14 '11 at 8:01
    
@lazyDBA Bull's eye, you got that exactly right. Thanks :) –  Deeptechtons Jul 14 '11 at 8:02
1  
You could do TOP 1..ORDER BY too –  gbn Jul 14 '11 at 10:44
    
@a_horse ... good catch - the query ran as was but you're right the column alias should have brackets as well. –  RThomas Jul 14 '11 at 16:13
1  
I think you should add that ROW is in the future Reserved Keywords list so it should be avoided as well. –  ypercube Dec 8 '13 at 8:02
  • If you want to get the exact count of rows in an efficient manner, then COUNT(*) is it. The ANSI standard (look for "Scalar expressions 125") states that COUNT(*) give the row count of a table: it is intended to be optimised from the start.

If COUNT(*) is specified, then the result is the cardinality of T.

  • A ROW_NUMBER() function isn't a practical option: it isn't a counting function (it's "ROW_NUMBER") and it will run badly as you add rows: a few 1000 will show how bad this it

  • SUM(1) may be optimised to COUNT(*) internally but I'd never use it

  • @@ROWCOUNT will require all rows to be returned by the first SELECT, which is a huge unnecessary overhead.

If you can live with approximate for SQL Server then use sys.dm_db_partition_stats. Marian's answer is out of date now since SQL Server 2005 added dmvs

SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'Mytable' AND (index_id < 2)

See this on SO too for some more info: http://stackoverflow.com/questions/6069237/fastest-way-to-count-exact-number-of-rows-in-a-very-large-table/6069288#6069288

In summary, there is exactly one useful way of getting the number of rows in a table. COUNT(*)

share|improve this answer
    
you are breaking Pots over my head. How come you got such insight into Sql and you have answered a few of my questions her on SE,SO –  Deeptechtons Jul 14 '11 at 12:02
2  
The right answer to the wrong question :) –  Jack Douglas Jul 14 '11 at 14:26
    
+1 for educational additions –  RThomas Jul 14 '11 at 16:08

There's another variant, without scanning the table, using system tables:

Select si.RowCnt
from sys.objects so join sysindexes si on si.id = so.object_id
where so.type_desc = 'USER_TABLE'
and si.indid in (0,1) -- heap or clustered index
and so.Name in ('Users')

Compare plans, durations, time.. You'll see it's a faster variant.

share|improve this answer
    
Presumably this does not give an exact count though? –  Jack Douglas Jul 14 '11 at 9:20
    
@Marian i think there is more easier way, SELECT Name FROM Users,SELECT @Output = @@ROWCOUNT what do you think –  Deeptechtons Jul 14 '11 at 9:20
    
@Deep - Marian's way is faster, not 'easier', because it doesn't scan the table at all. If you want 'easy', why not just select count(*) from users or select sum(1) from users? What have you got against count anyway? –  Jack Douglas Jul 14 '11 at 9:29
    
@Jack Douglas i got nothing against count, i wanted to learn other ways of getting the same done. Why rely on something all the time –  Deeptechtons Jul 14 '11 at 9:43
5  
@Deep - I've no problem with experimenting for academic interest, but to answer "Why rely on something all the time?", because a) it works, b) other people will be able to read your SQL c) you will be able to read your SQL when you come to debug it d) it is going to be faster than anything else you dream up (at least for exact answers) –  Jack Douglas Jul 14 '11 at 14:24

Change you query to :

SELECT MAX(A.Row) FROM 
(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) as Row FROM Users) as A

Try this mate.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.