Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old May 25th, 2007, 08:05 AM
Registered User
 
Join Date: May 2007
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default please give me a help

the two table
first table
studentmessage
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
as
departmentid departmentname personnumber
1 cs 4
2 math 2
3 english 1
4 china 1
null science 0


and
i want give a table than personnumber is max
as
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.
  #2 (permalink)  
Old May 25th, 2007, 08:09 AM
Registered User
 
Join Date: May 2007
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #3 (permalink)  
Old May 28th, 2007, 07:52 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========


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.