Group by problem I think.....

Hi,
I have a table called Eyeballs.
Within it I have the following fields.
EyeballID
CustomerID
EyeballDate
Price
Width
Lenght
I need to extract a list of all customers latest eyeball.
I have tried various group by statements but cannot seem to nail it.
Any help much appreciated.Try,
select *
from Eyeballs as a
where EyeballDate = (select max(b.EyeballDate) from Eyeballs as b where
b.EyeballID = a.EyeballID)
go
AMB
"doc" wrote:

> Hi,
> I have a table called Eyeballs.
> Within it I have the following fields.
> EyeballID
> CustomerID
> EyeballDate
> Price
> Width
> Lenght
> I need to extract a list of all customers latest eyeball.
> I have tried various group by statements but cannot seem to nail it.
> Any help much appreciated.
>|||>I need to extract a list of all customers latest eyeball
hahhahaa
It ain't pretty, but I think you're looking for something like:
select
EyeballID
CustomerID
max(eyeballdate) as eye_date
Price
Width
Lenght
from eyeballs
group by customerid|||group by customerid,EyeballID,Price,Width,Lenght (if necessary to display
those colums)
Jens.
<roy.anderson@.gmail.com> schrieb im Newsbeitrag
news:1113410931.602799.211200@.o13g2000cwo.googlegroups.com...
> hahhahaa
> It ain't pretty, but I think you're looking for something like:
> select
> EyeballID
> CustomerID
> max(eyeballdate) as eye_date
> Price
> Width
> Lenght
> from eyeballs
> group by customerid
>|||What is the primary key? Please post a CREATE TABLE statement for the
table, INCLUDING keys and other constraints, otherwise we just have to
guess at what you want. It also helps to post a few INSERT statements
of sample data and show your required end result.
An untested guess, assuming (customerid, eyeballdate) is unique:
SELECT eyeballid, customerid, eyeballdate, price, width, length
FROM Eyeballs AS T
WHERE eyeballdate =
(SELECT MAX(eyeballdate)
FROM Eyeballs
WHERE customerid = T.customerid)
David Portas
SQL Server MVP
--|||> It ain't pretty
... and it won't work either I'm afraid.
David Portas
SQL Server MVP
--