A SQL Query Question

See Thread at: http://www.techienuggets.com/Detail?tx=32975 Posted on behalf of a User

Hello everyone,

I have a table A:

userId long
picture MeduimBlob
datePosted DateTime

A userId can have many pictures posted. I want to write a query that returns a distinct userId along with the most recent picture posted. Can someone suggest an elegant and fast query to accomplish this?

Thanks

Adam



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: A SQL Query Question

See Thread at: http://www.techienuggets.com/Detail?tx=32975 Posted on behalf of a User

select userId, picture, MAX(datePosted) from A order by datePosted;





In Response To:

Hello everyone,

I have a table A:

userId long
picture MeduimBlob
datePosted DateTime

A userId can have many pictures posted. I want to write a query that returns a distinct userId along with the most recent picture posted. Can someone suggest an elegant and fast query to accomplish this?

Thanks

Adam

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: A SQL Query Question

>userId long
>picture MeduimBlob
>datePosted DateTime
>A userId can have many pictures posted. I want to write a
>query that returns a distinct userId along with the most
>recent picture posted. Can someone suggest an elegant and
>fast query to accomplish this?

Latest pic for user N:

SELECT userID,MAX(dateposted)
FROM tbl
WHERE userID=N;

Latest pics per user:

SELECT t1.userID,t1.dateposted
FROM tbl t1
LEFT JOIN tbl t2 ON t1.userID=t2.userID AND t1.dateposted WHERE t2.userID IS NULL;

PB

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org