Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Tricky sql-grouping for a photo-database needed

    Hi,

    i have the following table saving photos:

    +---------+--------+------------+
    | photoID | userID | datUpload |
    +---------+--------+------------+
    | 1700001 | 1 | 1235400142 |
    +---------+--------+------------+
    | 1700002 | 1 | 1235400154 |
    +---------+--------+------------+
    | 1700003 | 2 | 1235400158 |
    +---------+--------+------------+
    | 1700004 | 3 | 1235400161 |
    +---------+--------+------------+
    | 1700006 | 1 | 1235400167 |
    +---------+--------+------------+
    | 1700007 | 1 | 1235400169 |
    +---------+--------+------------+
    | 1700008 | 1 | 1235400175 |
    +---------+--------+------------+
    | 1700009 | 4 | 1235400182 |
    +---------+--------+------------+


    which is filled with photos (auto increment), the user and the date uploaded (and some other things).

    What i need as output is the following:

    +---------+--------+------------+----------+
    | photoID | userID | datUpload | aggCount |
    +---------+--------+------------+----------+
    | 1700009 | 4 | 1235400182 | 1 |
    +---------+--------+------------+----------+
    | 1700008 | 1 | 1235400175 | 3 |
    +---------+--------+------------+----------+
    | 1700004 | 3 | 1235400161 | 1 |
    +---------+--------+------------+----------+
    | 1700003 | 2 | 1235400158 | 1 |
    +---------+--------+------------+----------+
    | 1700002 | 1 | 1235400154 | 2 |
    +---------+--------+------------+----------+



    which means that i want only the latest photo from a user as long as there are no uploads from other users in between his uploads... and i need the number of photos which were uploaded in a row. (it is also enough if i knew that he uploaded more than one photo)

    BUT if a user upload i.e. 4 photos then another user upload 1 and then the first user upload another 2 the first user should come 2 times like FIRST user, SECOND user, FIRST user

    i have no idea, how this can be reached.

    thanks for your help.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I thought about this and couldn't think of any way to do it just with SQL. It would be pretty easy with a server language (PHP or whatever) though.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    Seems to me like you could do it in a stored procedure, by creating a temp table and then putting records into the temp table as you looped through individual records in the main table.

    But that would be as much code, and as slow, as just doing it in the server-side language, as Fumigator said.

    If this is something you do all the time, though, then maybe the right answer is to create the second table one time and then use a TRIGGER to keep it updated as new records are added to the main table? (Or, likely better, don't allow direct inserts into the main table; require that they be performed by way of a stored procedure.)

    Because no matter where it's done, creating this weird set of data is going to be an expensive operation. So better to do it once, upon insert of the data, than have to be continuously making a lengthy query.

  • #4
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    and what about an SQL-possibility to display only one or two photos of a user per day, but still to know if there are more than this one or two...

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    Sorry, do not understand that.

    Try describing again?

  • #6
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I mean i have for example

    Monday
    User 1 - 6 Photos
    User 2 - 7 Photos
    User 3 - 2 Photos
    User 4 - 1 Photo

    Tuesday
    User 1 - 2 Photos
    User 2 - 3 Photos
    User 3 - 1 Photos
    User 4 - 0 Photo

    Wednesday
    User 1 - 1 Photos
    User 2 - 0 Photos
    User 3 - 5 Photos
    User 4 - 3 Photo


    The result set should be:

    userID --- photo --- day --- more than 2
    -------------------------------------------------
    1 --- latest photo --- Wed --- no
    3 --- latest photo --- Wed --- yes
    3 --- penultimate photo --- Wed --- yes
    4 --- latest photo --- Wed --- yes
    4 --- penultimate photo --- Wed --- yes
    1 --- latest photo --- Tue --- no
    1 --- penultimate photo --- Tue --- no
    2 --- latest photo --- Tue --- yes
    2 --- penultimate photo --- Tue --- yes
    3 --- latest photo --- Tue --- no
    1 --- latest photo --- Mon --- yes
    1 --- penultimate photo --- Mon --- yes
    2 --- latest photo --- Mon --- yes
    2 --- penultimate photo --- Mon --- yes
    3 --- latest photo --- Mon --- no
    3 --- penultimate photo --- Mon --- no
    4 --- latest photo --- Mon --- no


    which means: from every user the latest 2 Photos per day with a flag that tells me, if there are more than two or not. Ordered by date desc.

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    May I ask why this all must be done with one query?

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    Looks like a fun one!

    I agree w/ Fumigator: Probably easier to do this with a combo of SQL and JSP/PHP/ASP/whatever coding. But...

    Let's give it a shot:

    First, the table:
    Code:
    Table: Photos
    photoid : autonumber [optional if photodate has date and time]
    userid : int, foreign key to users table
    photodate : datetime [assumed to contain both date and time when uploaded]
    photoname : file name on disk?  [or could have blob to hold photo]
    
    ...
    Then let's see what we come up with:
    Code:
    SELECT X.photonum, X.userid, X.theDay, X.pdate, X.howmany, pa.photoname
    FROM (
            SELECT 1 AS photonum, userid, DATE(photodate) AS theDay, MAX(photodate) AS pdate, COUNT(*) as howmany
            FROM photos
            GROUP BY userid, DATE(photodate)
                UNION 
            SELECT 2, p.userid, DATE(p.photodate), MAX(p.photodate), -1
            FROM photos AS p WHERE photodate NOT IN (
                SELECT MAX(p2.photodate) FROM photos AS p2
                WHERE p2.userid = p.userid 
                )
            GROUP BY p.userid, DATE(p.photodate)
         ) AS X,
         photos AS pa
    WHERE X.userid = pa.userid AND X.pdate = pa.photodate
    ORDER BY X.userid, X.theDay, X.photonum
    
    .
    If the photodate field has only the date, then we would NEED the photoid AUTOINCREMENT field, in order to distinguish the most recent and next most recent photo per day.

    Do you see why it works?

    First, we find the latest photo, per user, per day, along with the count of photos for that day. We add on the "1 AS photonum" to make sure that, when we finally order the records, this latest photo will come out in the list before...

    Second, we find the penultimate photo, per user, per day. To do this, we find the latest photo, per user, per day, *EXCEPTING* for the photo we found in the First step. [And if the inner SELECT doesn't work, as given, in MySQL, there are other ways to do this. Creating a VIEW would be a big help.]

    So we UNION those two SELECTs and we will have data something like this:
    Code:
    userid :: theDay :: photonum :: howMany :: pDate 
       33   :: Apr 4   ::     1    ::    4    :: Apr 4 7:32 PM
       33   :: Apr 4   ::     2    ::   -1    :: Apr 4 6:58 PM
       33   :: Apr 5   ::     1    ::    1    :: Apr 5 12:30 AM
    ...
    Finally, we JOIN that UNION result back to the Photos table, again, to pick up the photoname (or blob).

    Does that make sense?

    If you do have an AUTONUMBER photoid field in there, the query can be just a tiny bit simpler/faster, I think.

    Oh...if not obvious: If photonum is 1, then howMany will tell you how many total photos by that user on that day. (The number is meaningless when photonum is 2.)

  • #9
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    I wonder if a new col could be added to the table of photos. Call it 'upload_number'. So when a person uploads some pics, the insert statement checks for the max(upload_number) and (in time for the actual insert) it increments it. resultant table would be as shown below and the query would need only then select the max(upload_number) in order to do what the OP asks for.

    Code:
    +---------+--------+------------+-------------------+
    | photoID | userID | datUpload  |   upload_number   |
    +---------+--------+------------+-------------------+
    | 1700001 |   1    | 1235400142 |     5             |
    +---------+--------+------------+-------------------+
    | 1700002 |   1    | 1235400154 |     5             |  
    +---------+--------+------------+-------------------+
    | 1700003 |   2    | 1235400158 |     4             |
    +---------+--------+------------+-------------------+
    | 1700004 |   3    | 1235400161 |     3             |
    +---------+--------+------------+-------------------+
    | 1700006 |   1    | 1235400167 |     2             |
    +---------+--------+------------+-------------------+
    | 1700007 |   1    | 1235400169 |     2             |
    +---------+--------+------------+-------------------+
    | 1700008 |   1    | 1235400175 |     2             |
    +---------+--------+------------+-------------------+
    | 1700009 |   4    | 1235400182 |     1             |
    +---------+--------+------------+-------------------+
    Of course, I shall defer to the greater learning of the language by Fumigator and The Old Pedant.

    bazz
    Last edited by bazz; 04-25-2009 at 12:31 AM.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •