SQL question - grouping records and more

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL question - grouping records and more

Veysel Harun Sahin
Hi,

I have table with the fields id, name and number. In the table there are
several records with the same id and name but different numbers. For
example:

First record - id: 1, name: John, number: 5
Second record - id: 1, name: Joe, number: 4
Third record - id: 2, name: Richard, number: 1

I want to select records from the table by grouping with id field. I use
groupby for this. But i also want to see all the number fields' datas in a
group side by side. I have written down the list which i want to see after
the query.

First record - id: 1, name: John, number: 54
Second record - id: 2, name Richard, number: 1

Is there any idea about how to write a query to get this result?

Thanks in advace.

--
Veysel Harun Sahin
Reply | Threaded
Open this post in threaded view
|

Re: SQL question - grouping records and more

Igor Tandetnik
Veysel Harun Sahin wrote:

> I have table with the fields id, name and number. In the table there
> are several records with the same id and name but different numbers.
> For example:
>
> First record - id: 1, name: John, number: 5
> Second record - id: 1, name: Joe, number: 4
> Third record - id: 2, name: Richard, number: 1
>
> I want to select records from the table by grouping with id field. I
> use groupby for this. But i also want to see all the number fields'
> datas in a group side by side. I have written down the list which i
> want to see after the query.
>
> First record - id: 1, name: John, number: 54
> Second record - id: 2, name Richard, number: 1
>
> Is there any idea about how to write a query to get this result?

Not in plain SQL as far as I can tell. You can write a custom aggregate
function to concatenate the fields, then it becomes pretty
straightforward.

Igor Tandetnik

Reply | Threaded
Open this post in threaded view
|

Re: Re: SQL question - grouping records and more

Veysel Harun Sahin
Yes Igor. For now i do it with an aggreate function as you said. But i
thought that the code can be more simple and selection run faster by using
directly sql.

As you said i could not be able to find any info about sql to handle this
situation.

Thanks.

On 12/9/05, Igor Tandetnik <[hidden email]> wrote:

>
> Veysel Harun Sahin wrote:
> > I have table with the fields id, name and number. In the table there
> > are several records with the same id and name but different numbers.
> > For example:
> >
> > First record - id: 1, name: John, number: 5
> > Second record - id: 1, name: Joe, number: 4
> > Third record - id: 2, name: Richard, number: 1
> >
> > I want to select records from the table by grouping with id field. I
> > use groupby for this. But i also want to see all the number fields'
> > datas in a group side by side. I have written down the list which i
> > want to see after the query.
> >
> > First record - id: 1, name: John, number: 54
> > Second record - id: 2, name Richard, number: 1
> >
> > Is there any idea about how to write a query to get this result?
>
> Not in plain SQL as far as I can tell. You can write a custom aggregate
> function to concatenate the fields, then it becomes pretty
> straightforward.
>
> Igor Tandetnik
>
>


--
Veysel Harun Sahin