Professional Web Applications Themes

Grouping and returning rows - MySQL

Hi folks, I have a db which is similar to the example Name | Country | Type test | USA | Nm test2 | UK | Nb test3 | USA | Nc test4 | USA | Nm What I want to know is if there is a simple SQL statement to do the following: 1) Select all entries where country = USA (for example) 2) Group by Type 3) Display each group under each heading Type - i.e. Nm test | USA test4 | USA Nc test3 | USA I'm going to keep looking, but I want to keep my ...

  1. #1

    Default Grouping and returning rows

    Hi folks,

    I have a db which is similar to the example

    Name | Country | Type
    test | USA | Nm
    test2 | UK | Nb
    test3 | USA | Nc
    test4 | USA | Nm

    What I want to know is if there is a simple SQL statement to do the
    following:

    1) Select all entries where country = USA (for example)
    2) Group by Type
    3) Display each group under each heading Type - i.e.

    Nm
    test | USA
    test4 | USA

    Nc
    test3 | USA

    I'm going to keep looking, but I want to keep my SQL to a minimum and
    wondered what the official way of doing this would be.

    Thanks

    A

    UKuser Guest

  2. #2

    Default Re: Grouping and returning rows


    UKuser wrote:
     

    Item 3 is the tricky one, although it would be easy with a bit of php
    (or similar, presumably) provided that's available to you.

    Anyway, here's the query:

    SELECT type,name,country FROM table WHERE country = 'USA' GROUP BY type
    ORDER BY type

    strawberry Guest

  3. #3

    Default Re: Grouping and returning rows

    Hi,

    Thanks for the SQL. It was point 3 which I wanted to see if it could be
    done in the same SQL statement or if it was separate.

    A


    strawberry wrote: 
    >
    > Item 3 is the tricky one, although it would be easy with a bit of php
    > (or similar, presumably) provided that's available to you.
    >
    > Anyway, here's the query:
    >
    > SELECT type,name,country FROM table WHERE country = 'USA' GROUP BY type
    > ORDER BY type[/ref]

    UKuser Guest

  4. #4

    Default Re: Grouping and returning rows

    UKuser wrote: 

    It _can_ be done, with generated SQL in stored procedures and temporary
    tables, but it's usually not worth the overhead of writing,
    maintaining, and securing it. I've never tried to execute generated SQL
    in a MySQL stored procedure, so I'm not even sure it has a function to
    do so.

    --Thomas

    tgmayfield Guest

  5. #5

    Default Re: Grouping and returning rows

    UKuser wrote:
     


    I always get confused when folks talk about SQL results as what
    is "displayed".
    I think more in set-based logic, or how it's structured.

    What do you mean when you say "Display each group under each
    heading"?
    Sanders Guest

  6. #6

    Default Re: Grouping and returning rows

    strawberry wrote:
     

    I get all muddle-headed with this whole GROUP BY thing.
    What does the record of a GROUP BY query look like? Does it
    have a special column, or go beyond just two-dimensional tables?
    Sanders Guest

  7. #7

    Default Re: Grouping and returning rows

    Hi Folks,

    Thanks for your advice. I've solved the problem by doing the following:

    1) SQL(1) to select distinct headers from database
    2) Print SQL(1)->headers
    3) Execute a second (sub) SQL statement SQL(2) *foreach* SQL(1)
    4) Print SQL(2)->name (i.e. unique records)
    5) SQL(2)->MoveNext();
    6) SQL(1)->MoveNext();

    I'm using ADODB and can HIGHLY recommend it as I'm working inhouse with
    Access and live will be MySQL and can change with a very simple coding
    switch. Its probably not pushing the db to its limits (i.e.
    transactions and stuff which could be restricted by ADODB) but for
    small-medium dbs (and probably some larger ones) is great.

    Anyway - hope my thoughts help. I've not posted the code as its ADODB
    and probably irrelevant for most of you.

    Cheers

    A

    Sanders Kaufman wrote:
     
    >
    > I get all muddle-headed with this whole GROUP BY thing.
    > What does the record of a GROUP BY query look like? Does it
    > have a special column, or go beyond just two-dimensional tables?[/ref]

    UKuser Guest

  8. #8

    Default Re: Grouping and returning rows

    tgmayfield wrote: 

    FYI:
    http://dev.mysql.com/doc/refman/5.0/en/sqlps.html


    Regards,
    Bill K.
    Bill Guest

Similar Threads

  1. ODBC Sybase driver returning zero rows
    By ColonelNusterd in forum Coldfusion Database Access
    Replies: 1
    Last Post: April 18th, 01:55 PM
  2. Returning 0 rows from a PL/PGSQL
    By Vitaly in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: February 20th, 09:59 PM
  3. Returning Data Rows from WS in different Formats Problem
    By Ray Parker in forum ASP.NET Web Services
    Replies: 0
    Last Post: July 2nd, 07:02 AM
  4. returning results in rows
    By Ken in forum ASP
    Replies: 2
    Last Post: September 17th, 02:15 PM
  5. Select Intermittently Returning Incorrect Number of Rows
    By Karsten Farrell in forum Oracle Server
    Replies: 0
    Last Post: January 13th, 06:48 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