SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    l 0 l silver trophybronze trophy lo0ol's Avatar
    Join Date
    Aug 2002
    Location
    Palo Alto
    Posts
    5,329
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Trends in a Table

    So let's say I have a table called "BoringTable", and I have rows in it-

    Code:
    -ID-      -Name-
    1            A
    2            B
    3            A
    4            F
    5            R
    6            B
    What I need to do is print out the different names on a page. I don't want repeats, however. So the above would print out A, B, F, R. I assume there's some easy way to do this that my limited knowledge doesn't know.

    Thanks!

  2. #2
    Now with customized title Jump's Avatar
    Join Date
    Sep 2002
    Location
    The Restaurant at The End of The Universe
    Posts
    1,423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $result mysql_query("SELECT DISTINCT(name) FROM BoringTable"); 

  3. #3
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    DISTINCT isn't a function, so I'm not sure if that query would work. Try this, it'll do that trick
    Code:
    SELECT DISTINCT
    	id
    	, name
    FROM
    	BoringTable

  4. #4
    Now with customized title Jump's Avatar
    Join Date
    Sep 2002
    Location
    The Restaurant at The End of The Universe
    Posts
    1,423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works all the time for me.

  5. #5
    SitePoint Addict Messiah's Avatar
    Join Date
    Jun 2001
    Location
    Bloomington, In.
    Posts
    216
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since when hasn't DISTINCT() been a function? Link
    Messiah | Ink-Press: web publishing simplified!

  6. #6
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    Well I guess DISTINCT is a function with MySQL but I suspect it's proprietary and not a "function" but rather an "expression" in standard SQL. Not 100% sure on this though.

  7. #7
    l 0 l silver trophybronze trophy lo0ol's Avatar
    Join Date
    Aug 2002
    Location
    Palo Alto
    Posts
    5,329
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Heehee, I've started a heated debate.

    Thanks you all!

  8. #8
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by lo0ol
    Heehee, I've started a heated debate.
    Debate over, DISTINCT is not a function

  9. #9
    l 0 l silver trophybronze trophy lo0ol's Avatar
    Join Date
    Aug 2002
    Location
    Palo Alto
    Posts
    5,329
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hmm... I've been fiddling around with it and it works well. The only thing now I have left is that I want to be able to sort through the received data, based upon how many occurances there are in the database. So I want to pull up a list of all the unique items in a column (which I can do), but now I want to sort it based upon how many occurances there are. I'd also like to sort alphabetically as well, if possible.

  10. #10
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    SitePoint does have a database forum, where these questions will get answered a lot more effectivly. Sorting alphabetically is easy using an ORDER BY clause but sorting by occurances is something that is beyond me, but if you post in the DB forum you will get a much more definitive answer

  11. #11
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DJ P@CkMaN
    DISTINCT isn't a function, so I'm not sure if that query would work. Try this, it'll do that trick
    Code:
    SELECT DISTINCT
    	id
    	, name
    FROM
    	BoringTable
    This does not meet the original poster's initial request. If we assume that ID is a primary key, then by definition, we know that all values of ID will be distinct.

    As already posted,

    SELECT DISTINCT name
    FROM BoringTable

    is the solution.

    For the solution to the second request. I will assume you want to order in descending order based on how many occurances there are (that is the name with the highest occurances is ranked higher in the result set).

    SELECT name, count(*) AS colcount
    FROM BoringTable
    GROUP BY name
    ORDER BY colcount DESC , name

    should do the trick.

  12. #12
    l 0 l silver trophybronze trophy lo0ol's Avatar
    Join Date
    Aug 2002
    Location
    Palo Alto
    Posts
    5,329
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by freakysid
    This does not meet the original poster's initial request. If we assume that ID is a primary key, then by definition, we know that all values of ID will be distinct.

    As already posted,

    SELECT DISTINCT name
    FROM BoringTable

    is the solution.

    For the solution to the second request. I will assume you want to order in descending order based on how many occurances there are (that is the name with the highest occurances is ranked higher in the result set).

    SELECT name, count(*) AS colcount
    FROM BoringTable
    GROUP BY name
    ORDER BY colcount DESC , name

    should do the trick.
    Sorry to bother you all again. This thread has been immensely helpful to me thus far- I haven't dabbled in these types of queries yet, and I've really been a learning a lot.

    freakysid, that works well for me. But one thing I'd like it to further do it count yet another part of a table. Let me give you another example-

    Say I have the same table as before, except now it has a column 'numbers', we'll say. So each row would have a different, randomish number. I'd like to do as before and sort by how many references in the table there are, but also I'd like to be able to click on a link that changes it so it sorts by the highest sum of 'numbers' per name. So, to recap, for that second query it would sort as before by each name, sum up all of the numbers related to that name, then print out the list of names ordered by the sum of numbers relating to that name. I'll probably just make this 2 different queries (the one freakysid posted) and the new query and do an if/else on it based on what the user selects.

    I hope that came out semi-coherant. If you have any questions I'll try to clear them up for you. Again, thanks a whole lot for all of your help.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    nope, your explanation didn't do anything for me, sorry

    how about some sample rows of your table, and some sample rows that you wnt to see in the result of the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by r937
    nope, your explanation didn't do anything for me, sorry
    oh, wait, i think i get it
    Code:
    select name
         , sum(numbers) as sumnumbers
      from boringtable
    group 
        by name
    order 
        by sumnumbers desc 
         , name
    sorry for the brain cramp

    yes, two different queries, unless by "clicking" on the link what you do is dynamically decide which result is desired, and generate the query "on the fly" as it were, changing just the one column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    l 0 l silver trophybronze trophy lo0ol's Avatar
    Join Date
    Aug 2002
    Location
    Palo Alto
    Posts
    5,329
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    There we go. Had to fiddle with some excess code on my end, but I finally figured it out. Thanks a bundle!


Bookmarks

Posting Permissions

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