Newby SQL Question

Newby SQL Question

Post by Frederic de Comarmon » Thu, 07 Oct 1999 04:00:00



Hi all,

I am using an Access97 database and I am trying to extract some info from my
tables.
I got a table call "catch" that stores the  following:

catchID (the key field)
Angler
fishID
weight

I need to create a SQL statement that will return the max weights for the
different fish types (i.e. fishID) and also display angler's name.

using the following statement I have managed to get the related top weights
with their fishID but cannot display the anglers name.

SELECT fishID,max(weight)
FROM catch
GROUP BY fishID

results:
fishID    weight
1            1000
2            500
3            600

Please help.
Thanks
Fred

 
 
 

Newby SQL Question

Post by Charles Bretana Jr » Thu, 07 Oct 1999 04:00:00


Frederic,

    Typical problem whose solution involves subquery...
1) Think of it as a English question...
    " I Want the fisherman's name, and the weight,  of the heaviest fish
caught for each type of fish caught. "

Select OC.FishID, OC.Angler, OC.Weight
    From Catch OC
    Where OC.Weight =
        (Select Max(Weight)
          From Catch IC
          Where IC.FishID = OC.FishID)

Regards,

Charly



Quote:> Hi all,

> I am using an Access97 database and I am trying to extract some info from
my
> tables.
> I got a table call "catch" that stores the  following:

> catchID (the key field)
> Angler
> fishID
> weight

> I need to create a SQL statement that will return the max weights for the
> different fish types (i.e. fishID) and also display angler's name.

> using the following statement I have managed to get the related top
weights
> with their fishID but cannot display the anglers name.

> SELECT fishID,max(weight)
> FROM catch
> GROUP BY fishID

> results:
> fishID    weight
> 1            1000
> 2            500
> 3            600

> Please help.
> Thanks
> Fred


 
 
 

Newby SQL Question

Post by Frederic de Comarmon » Thu, 07 Oct 1999 04:00:00


Thanks a lot Charly.

I was never taught that OC IC stuff that you use.
Great it works !


>Frederic,

>    Typical problem whose solution involves subquery...
>1) Think of it as a English question...
>    " I Want the fisherman's name, and the weight,  of the heaviest fish
>caught for each type of fish caught. "

>Select OC.FishID, OC.Angler, OC.Weight
>    From Catch OC
>    Where OC.Weight =
>        (Select Max(Weight)
>          From Catch IC
>          Where IC.FishID = OC.FishID)

>Regards,

>Charly

 
 
 

Newby SQL Question

Post by Charles Bretana Jr » Thu, 07 Oct 1999 04:00:00


Frederick,

    The OC & IC stuff are just aliases for the tables, so that the same
table can be used in various place s within a SQL query, aliased
differently, and thereby allowing you to reference fields from the various
"instances" of the table disciminately.

I chose those 2 character strings ('IC' and 'OC') to represent the two
tables as "Inner Catch"  and "Outer Catch"  tables, respectively, but any 2
strings would do equally well......

Regards,

Charly



> Thanks a lot Charly.

> I was never taught that OC IC stuff that you use.
> Great it works !


> >Frederic,

> >    Typical problem whose solution involves subquery...
> >1) Think of it as a English question...
> >    " I Want the fisherman's name, and the weight,  of the heaviest fish
> >caught for each type of fish caught. "

> >Select OC.FishID, OC.Angler, OC.Weight
> >    From Catch OC
> >    Where OC.Weight =
> >        (Select Max(Weight)
> >          From Catch IC
> >          Where IC.FishID = OC.FishID)

> >Regards,

> >Charly

 
 
 

1. ?Miscellaneous Newby Stupid Questions

Using SQL Server 2000...

1. The enterprise manager shows my server as "DELL500 (Windows NT)".  Is
"(Windows NT)" part of the name, or is my server called just "DELL500"?

2. I use Windows only authentication, and the startup service account is
System Account.  When I run osql, what password do I use?  Currently the
error I get is:

    c:\log4>osql /U administrator /P
    Login failed for user 'administrator'. Reason: Not associated with a
trusted
    SQL Server connection.

3. In my C++ app I open a connection like this:

    CString ConnectionString = (CString) "Provider=sqloledb;Data
Source=;Integrated Security='SSPI';Initial Catalog=Northwind;User
Id=;Password=";

    VARIANT ConnectionVariant;
    CConnection Connection;

     if(!Connection.CreateDispatch(_T("ADODB.Connection")))
        exit(1);

        Connection.Open(ConnectionString, "", "", NULL);

How does it know which server to use?  What does SSPI refer to?

4. When I execute the following:

            Connection.Execute("CREATE DATABASE MyCDatabase", &Dummy, 0);

It works fine, but throws an exception.  Is that normal?

5. When I execute this:

        Connection.Execute("CREATE TABLE test.FromC(job_id  smallint
IDENTITY(1,1)   PRIMARY KEY CLUSTERED)", &Dummy, 0);

I get the error: "Specified owner name 'test' either does not exist or you
do not have permission to use it."  How should this command be written?

Thanks!

2. Shared memory connections (NETTYPE)

3. Newby Query Question

4. select "tdm: set sql_max_rows = 10"

5. Newby SP result set question

6. share database

7. newby trigger question

8. vb5 print screen code?

9. newby question about copying database

10. newby stored procedure question

11. Newby Query Question

12. Newby Trigger question

13. Newby Question ... Removing a Column