MAX function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MAX function

Author

Topic

risajef
Starting Member

1 Post

Posted - 2015-03-14 : 10:52:43

Hello

I have a problem. My Query:


SELECT MAX(books_lent), reader_id
FROM (SELECT count(*) AS books_lent, reader_id
      FROM borrow
      GROUP BY reader_id) AS nr_lent
GROUP BY reader_id

Selects all readers and not only the one that have lent the most books.


max ; reader_id
1   ; 1045
1   ; 185
1   ; 1149
1   ; 723
1   ; 671
3   ; 388
1   ; 652

I hope the query is clear and would be thankful if someone can solve my problem.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-14 : 15:37:48

[code]SELECT TOP(1) *
FROM (SELECT count(*) AS books_lent, reader_id
FROM borrow
GROUP BY reader_id) AS nr_lent
ORDER BY books_lent DESC[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-15 : 02:24:34

[code]SELECT TOP(1) WITH TIES
COUNT(*) AS Books_Lent,
Reader_ID
FROM dbo.Borrow
GROUP BY Reader_ID
ORDER BY COUNT(*) DESC;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources