SQL Query Help!

[Armitage]

I have a table something like this:

A 1 john
A 2 jeff
A 4 jill
C 2 jane
C 6 jim
C 8 jason
D 1 julie
D 3 josh
D 5 janet

What I want in the result is the row for each unique entry in the first column (A, C, D) and its largest value in the second. So executing the query on the above table would result in:

A 4 jill
C 8 jason
D 5 janet

Is this possible in a single query? It seems that it should be easy, but I can't see it.
Thanks

edit: Oops, I realized my original 2 column example was trivial.

 

[manly]

I'm pretty sure it's possible, but I'm still thinking of a simple, clean way to do it. Declarative programming with SQL can be challenging.

 

[Mucman]

Intersting query I will take a look when I get home later tonight if it's not answered by then...

How about this?

SELECT DISTINCT columnA, Max(columnB), columnC
FROM tableNAME

*nevermind* that query didn't work...

 

[Armitage]

Originally posted by: Mucman
Intersting query I will take a look when I get home later tonight if it's not answered by then...

How about this?

SELECT DISTINCT columnA, Max(columnB), columnC
FROM tableNAME

Nope.
For one thing, you need a 'GROUP BY' clause to go with the max() call.
If you add 'GROUP BY columnA' it just returns columnA, max(columnB) and the first entry in the table for columnC:

A 4 john
C 8 jane
D 5 julie

I keep thinking this has got to be simple! It probably comes up all the time.
But I'm in the process of wrapping a script around it because I can't figure it out!

 

[manly]

The table schema is:

ERGEORGE
--------------
ID int
GRP varchar(20)
FNAME varchar(30)

Try this query:

select * from ERGEORGE e1
where e1.ID = (select MAX(ID) from ERGEORGE e2
group by e2.GRP
having e2.GRP = e1.GRP);

I haven't really tested it out yet, but it gives the right result for your test tuples. I still don't know how to embed white-space into forum postings (singh has a little script to do it).

 

[Armitage]

Thanks manly, but I should have mentioned that I'm using MySQL which, to my knowledge, doesn't support the type of construct you suggest.

 

[manly]

Still no nested subqueries in MySQL?

 

[Mucman]

You can't do sub subqueries in mySQL? I guess I've been spoiled by learning database stuff on SQL Server 2000...

 

[DT4K]

I haven't tested this, but I've heard of people using temp tables to do the same stuff you would do with a subquery.
So here's an idea:

CREATE TEMPORARY TABLE myTempTable
SELECT field1, MAX(field2) as maxField2 FROM yourOriginalTable GROUP BY field1;

SELECT field1, field2, field3
FROM yourOriginalTable, myTempTable
WHERE yourOriginalTable.field1 = myTempTable.field1
AND yourOriginalTable.field2 = myTempTable.maxField2

Let us know if something like this works.
I have also been spoiled by working in SQL Server 2000. We've probably got almost as much code in our stored procedures as we do in the VB client apps.