Archived

This topic is now archived and is closed to further replies.

SQL: i want max of column, but also display other details

This topic is 4945 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi ive got this table 'ticket' And i want to get the max of ticketcount aswell as output the route name. eg Watsons Bay/Taronga Zoo and 361 but i get errors when i try this: select route ,max(ticketcount) from ticket ; What should i do? heres my table thanks
ROUTE                                             TICKETCOUNT

------------------------------------------------- -----------

Circular Quay/Mosmon                                       84

Darling Harbour/Taronga Zoo                               115

Manly/Circular Quay                                        85

Manly/Darling Harbour                                     124

Mosmon/Neutral Bay                                         72

Neutral Bay/Darling Harbour                                44

Taronga Zoo/Woolwich                                        9

Watsons Bay/Neutral Bay                                    32

Watsons Bay/Taronga Zoo                                   361

Woolwich/Darling Harbour                                    3

[edited by - johnnyBravo on May 31, 2004 2:59:53 AM] [edited by - johnnyBravo on May 31, 2004 3:00:23 AM]

Share this post


Link to post
Share on other sites

select route, ticketcount from ticket
where ticketcount = (select max(ticketcount) from ticket)


shmoove

[edited by - shmoove on May 31, 2004 3:23:24 AM]

Share this post


Link to post
Share on other sites
Use "group by". For example:

select route, max(ticketcount)
from ticket
group by route

You need to use "group by" on non-aggregate columns in your select clause when you want to have specific column values alongside aggregated values. You can also use a "having" clause if you want to filter on the aggregated value. For example:

select route, max(ticketcount)
from ticket
group by route
having max(ticketcount) > 50

If you''re using SQL Server, check Books Online for more information on "group by" and "having"

Share this post


Link to post
Share on other sites