Please help with this query "group by"/ "distinct"

Discussion in 'MySQL' started by JEET, Feb 25, 2012.

0
  1. #1
    These are for mysql used in a website I'm making using php
    Just to clarify, the table is very large, so I want to avoid using multiple queries.
    Thanks :)Hi,
    I have a table like this:
    ID, TITLE, TYPE

    TYPE column has 2 types of records namely: "sent" & "from"

    What I am trying to do is select latest 3 records of "each" type from this table using 1 single query. Unfortunately, all I am getting is 1 record of each type...
    Here's the query:

    "select * from table group by TYPE order by ID desc limit 6"

    That gives just 1 record of "sent" type and 1 record of "from" type (total 2, doesn't matter how I change the "limit" part of query)
    What can I do to select 3 records of "sent" type and 3 records of "from" type using minimal number of queries?
    Please give me the query if possible.
    Thanks :)
     
    Last edited: Feb 25, 2012
    JEET, Feb 25, 2012 IP
  2. Pepdeal

    Pepdeal Peon

    Messages:
    149
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    what is these for?
     
    Pepdeal, Feb 25, 2012 IP
  3. sonic10

    sonic10 Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    SELECT * from tablename WHERE type = 'sent' OR type = 'from' order by ID desc limit 6;
     
    sonic10, Feb 25, 2012 IP
  4. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #4
    Try this (no guarantee):
    

select * from table where type = 'sent' order by ID limit 3

union select * from table where type = 'from' order by ID limit 3

group by type;
    Code (markup):
     
    Rukbat, Feb 25, 2012 IP
  5. JEET

    JEET Notable Member

    Messages:
    3,832
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #5
    The above won't work because it would select all 6 records of "sent" if they appear first in table... ( or all 6 records of "from" if they appear first )


    That would work I think, but why is it grouping if I already have 3 records of each?
    Also I'm not sure, but is that query reading the table twice (start to end)?

    Thanks
     
    JEET, Feb 25, 2012 IP
  6. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #6
    Yolu don't need grouping if you don't care in what order the records are. Or you could order by ID, type or type, ID depending on what you want to see.
     
    Rukbat, Feb 25, 2012 IP