Professional Web Applications Themes

select distinct, but order by number of occurances? - MySQL

How can I express this in a simple way? Say I have a bunch of products from around 10 manufacturers, and I'd like to list manufacturers ordered by number of products in the database. The manufacturer with most products in the database should be first, etc. I'm aware I can do this in 10 different ways but I'd like to do it in a single query that won't be too slow....

  1. #1

    Default select distinct, but order by number of occurances?

    How can I express this in a simple way? Say I have a bunch of products
    from around 10 manufacturers, and I'd like to list manufacturers
    ordered by number of products in the database. The manufacturer with
    most products in the database should be first, etc.

    I'm aware I can do this in 10 different ways but I'd like to do it in a
    single query that won't be too slow.

    snapcount@gmail.com Guest

  2. #2

    Default Re: select distinct, but order by number of occurances?

    com wrote: 

    Impossible to say without knowing your schema. Sample data would help,
    also.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: select distinct, but order by number of occurances?

    >How can I express this in a simple way? Say I have a bunch of products 

    It sounds like you want something like:

    SELECT manufacturer, count(product) as cnt from mfgr_products
    group by manufacturer order by cnt desc;

    but without a schema it's hard to tell.
    Gordon Guest

  4. #4

    Default Re: select distinct, but order by number of occurances?

    Yeah, that was it. Works perfectly. Thanks for your help!


    Gordon Burditt wrote: 
    >
    > It sounds like you want something like:
    >
    > SELECT manufacturer, count(product) as cnt from mfgr_products
    > group by manufacturer order by cnt desc;
    >
    > but without a schema it's hard to tell.[/ref]

    snapcount@gmail.com Guest

Similar Threads

  1. Using SELECT DISTINCT
    By The Ox in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 16th, 10:18 PM
  2. SELECT DISTINCT from two tables
    By Marco Alting in forum ASP
    Replies: 4
    Last Post: July 31st, 10:26 AM
  3. Select Distinct
    By Scott in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 3rd, 05:22 PM
  4. Replies: 3
    Last Post: April 18th, 12:52 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139