Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 25th, 2007, 08:05 AM
Registered User
Join Date: May 2007
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default please give me a help

the two table
first table
id departmentid
1 1
2 1
3 1
4 1
5 2
6 2
7 3
8 4

second table departmentmessage
departmentid departmentname
1 cs
2 math
3 english
4 china
5 science

i want give a table
departmentid departmentname personnumber
1 cs 4
2 math 2
3 english 1
4 china 1
null science 0

i want give a table than personnumber is max
departmentid departmentname personnumber
1 cs 4

how to solve the problem , who help me .
thanks in advances

i am a novice, i am a chinese, i hope everyone can help me in here.
Old May 25th, 2007, 08:09 AM
Registered User
Join Date: May 2007
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts

i can use join or left join or right join to solve the problew, who give me the second way , wo want to subquery

Old May 28th, 2007, 07:52 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi

hi there.. depending on your database the syntax could not be the same..

but the idea is:
select b.departmentname,a.datacount,a.departmentid from(
select count(*) as datacount,departmentid from studentmessage group by departmentid) as a inner join departmentmessage as b on a.departmentid = b.departmentid

this will give you all the records, to find the first just order and get only the first one (select top 1 (SQL server, access), where rownum < 2 (ORACLE))

Note that the instruction could be a little wrong I don't have a way to test it...



================================================== =========
Read this if you want to know how to get a correct reply for your question:
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
================================================== =========
I know that CVS was evil, and now i got the proof:
================================================== =========

Similar Threads
Thread Thread Starter Forum Replies Last Post
Please give me solution deepak.deemca C++ Programming 1 October 17th, 2007 04:27 PM
please give me a help wujilin SQL Server 2005 2 June 2nd, 2007 04:33 PM
give me solution asudhakar ASP.NET 1.0 and 1.1 Basics 2 March 19th, 2007 09:33 AM
How to give the value to a where - between san22 Pro VB.NET 2002/2003 1 May 5th, 2006 11:55 AM
Help!Give me some advices! yuyijq General .NET 2 March 17th, 2005 08:06 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.