Xojo Conferences
MBSOct2019CologneDE

RBDB: user order by AND group by? (Real Studio network user group Mailinglist archive)

Back to the thread list
Previous thread: [ANN] 666 Killer
Next thread: Test, please ignore (list down?)


Re: Pigmy 2.0   -   Jan Erik Moström <
  RBDB: user order by AND group by?   -   Paul
   Re: RBDB: user order by AND group by?   -   Norman Palardy
    Re: RBDB: user order by AND group by?   -   Paul
     Re: RBDB: user order by AND group by?   -   Norman Palardy
    Re: RBDB: user order by AND group by?   -   Jonk.van.der.Kogel esa.int

RBDB: user order by AND group by?
Date: 29.05.02 15:23 (Wed, 29 May 2002 09:23:53 -0500)
From: Paul
Thanks, Seth Willits, for your RealDatabase guide.

What I need to know now is not answered there, though.

Can I use *both* "order by" and "sort by" in an SQL select statement?
Or, are they mutually exclusive??

What I want to be able to do is group results and then order them. For
instance, I'd like to be able to "GROUP BY jobtype ORDER BY salary"...

This would put all the accountants together, sorted by their salary...
... put all the programmers together, sorted by their salary...
Etc...

When I use both group by and order by together in a select statement...
I get NO results returned... but I also get no database error.

Thanks for any assistance,
Paul K

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: RBDB: user order by AND group by?
Date: 29.05.02 15:51 (Wed, 29 May 2002 08:51:55 -0600)
From: Norman Palardy
In a compliant implementation of SQL 92 you can use them together

Since RB's built in database is NOT fully SQL 92 compliant the short
answer is .... NO ... you cant use them together in any release up to
4.0.2

On Wednesday, May 29, 2002, at 08:23 AM, Paul wrote:

> Thanks, Seth Willits, for your RealDatabase guide.
>
> What I need to know now is not answered there, though.
>
> Can I use *both* "order by" and "sort by" in an SQL select statement?
> Or, are they mutually exclusive??
>
> What I want to be able to do is group results and then order them. For
> instance, I'd like to be able to "GROUP BY jobtype ORDER BY salary"...
>
> This would put all the accountants together, sorted by their salary...
> ... put all the programmers together, sorted by their salary...
> Etc...
>
> When I use both group by and order by together in a select statement...
> I get NO results returned... but I also get no database error.
>
> Thanks for any assistance,
> Paul K
>
> ---
> Subscribe to the digest:
> <mailto:<email address removed>>
> Unsubscribe:
> <mailto:<email address removed>>

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: RBDB: user order by AND group by?
Date: 29.05.02 17:02 (Wed, 29 May 2002 11:02:42 -0500)
From: Paul
Okay...

It seems like it might be working (at least not erroring...) if I change
"group by" to "groupby"... (i.e. no space...)

One final question...
My database in question has two tables.
I am trying to do a simple "find all the records in both tables" as a test...

"select * from tableone, tabletwo"

and when I do this I get no results.

I do get expected results if I search only one of the two tables.

Can I not combine the tables in a SQL search through RealBasic?

Thanks for the handholding... I've just been using this online reference
to SQL:
http://fluffycat.com/sql/

and finding these little RB nuances. There are just times that I'm not
sure if it is an RB shortcoming or if I have built my SQL string wrong.

Regards,
Paul Kaiser

Norman Palardy wrote:
>
> In a compliant implementation of SQL 92 you can use them together
>
> Since RB's built in database is NOT fully SQL 92 compliant the short
> answer is .... NO ... you cant use them together in any release up to
> 4.0.2
>
> On Wednesday, May 29, 2002, at 08:23 AM, Paul wrote:
>
> > Thanks, Seth Willits, for your RealDatabase guide.
> >
> > What I need to know now is not answered there, though.
> >
> > Can I use *both* "order by" and "sort by" in an SQL select statement?
> > Or, are they mutually exclusive??
> >
> > What I want to be able to do is group results and then order them. For
> > instance, I'd like to be able to "GROUP BY jobtype ORDER BY salary"...
> >
> > This would put all the accountants together, sorted by their salary...
> > ... put all the programmers together, sorted by their salary...
> > Etc...
> >
> > When I use both group by and order by together in a select statement...
> > I get NO results returned... but I also get no database error.
> >
> > Thanks for any assistance,
> > Paul K
> >
> >
> > ---
> > Subscribe to the digest:
> > <mailto:<email address removed>>
> > Unsubscribe:
> > <mailto:<email address removed>>
> >
> ---
> Subscribe to the digest:
> <mailto:<email address removed>>
> Unsubscribe:
> <mailto:<email address removed>>

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: RBDB: user order by AND group by?
Date: 29.05.02 17:37 (Wed, 29 May 2002 10:37:37 -0600)
From: Norman Palardy
You'll get a cartesian product as a result set (every row on table 1
matched with every row of table2) which is probably not what you expect

ie/
table1 table2
column1 column2
1 4
2 5
3 6

your query should result in

column1 column2
1 4
1 5
1 6
2 4
2 5
2 6
3 4
3 5
3 6

If what you want is

1
2
3
4
5
6

you need to have column aliases and probably a union clause so the query
looks like

select "somecolumn" column1 from table1
union
select "somecolumn" column2 from table2

This makes each query rename the column being selected (column1 in the
first select and column2 in the second) to a new name (somecolumn)
and then the 2 results can be put together into a single result set

You can NOT do this in RB's SQL yet as it does not support unions

On Wednesday, May 29, 2002, at 10:02 AM, Paul wrote:

> Okay...
>
> It seems like it might be working (at least not erroring...) if I change
> "group by" to "groupby"... (i.e. no space...)
>
> One final question...
> My database in question has two tables.
> I am trying to do a simple "find all the records in both tables" as a
> test...
>
> "select * from tableone, tabletwo"
>
> and when I do this I get no results.
>
> I do get expected results if I search only one of the two tables.
>
> Can I not combine the tables in a SQL search through RealBasic?
>
> Thanks for the handholding... I've just been using this online reference
> to SQL:
> http://fluffycat.com/sql/
>
> and finding these little RB nuances. There are just times that I'm not
> sure if it is an RB shortcoming or if I have built my SQL string wrong.
>
> Regards,
> Paul Kaiser
>
> Norman Palardy wrote:
>>
>> In a compliant implementation of SQL 92 you can use them together
>>
>> Since RB's built in database is NOT fully SQL 92 compliant the short
>> answer is .... NO ... you cant use them together in any release up to
>> 4.0.2
>>
>> On Wednesday, May 29, 2002, at 08:23 AM, Paul wrote:
>>
>>> Thanks, Seth Willits, for your RealDatabase guide.
>>>
>>> What I need to know now is not answered there, though.
>>>
>>> Can I use *both* "order by" and "sort by" in an SQL select statement?
>>> Or, are they mutually exclusive??
>>>
>>> What I want to be able to do is group results and then order them. For
>>> instance, I'd like to be able to "GROUP BY jobtype ORDER BY salary"...
>>>
>>> This would put all the accountants together, sorted by their salary...
>>> ... put all the programmers together, sorted by their salary...
>>> Etc...
>>>
>>> When I use both group by and order by together in a select
>>> statement...
>>> I get NO results returned... but I also get no database error.
>>>
>>> Thanks for any assistance,
>>> Paul K
>>>
>>>
>>> ---
>>> Subscribe to the digest:
>>> <mailto:<email address removed>>
>>> Unsubscribe:
>>> <mailto:<email address removed>>
>>>
>> ---
>> Subscribe to the digest:
>> <mailto:<email address removed>>
>> Unsubscribe:
>> <mailto:<email address removed>>
> ---
> Subscribe to the digest:
> <mailto:<email address removed>>
> Unsubscribe:
> <mailto:<email address removed>>

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>

Re: RBDB: user order by AND group by?
Date: 29.05.02 16:30 (Wed, 29 May 2002 17:30:59 +0200)
From: Jonk.van.der.Kogel esa.int


From: Jonck van der Kogel@ESA on 05/29/2002 05:30 PM

Though I know you can use both GROUP BY and ORDER BY in one statement in,
let's say, mySQL, I'm not sure whether RB supports it. I have heard that RB
only supports a relatively small subset of SQL syntax, so maybe you would
have to use two queries to get the result you are looking for.
I guess this doesn't really help you at all :-) (sorry!) but I wanted to let
you know anyway.
Bye, Jonck

|--------+------------------------>
| | Paul |
| | <buddy@goodlea|
| | rning.com> |
| | |
| | 29/05/2002 |
| | 16:23 |
| | Please respond|
| | to "REALbasic |
| | Network Users |
| | Group" |
| | |
|--------+------------------------>
>-------------------------------------------------------------------------|
| |
| To: RBNUG <<email address removed>> |
| cc: (bcc: Jonck van der Kogel/estec/ESA) |
| Subject: RBDB: user order by AND group by? |
>-------------------------------------------------------------------------|



Thanks, Seth Willits, for your RealDatabase guide.

What I need to know now is not answered there, though.

Can I use *both* "order by" and "sort by" in an SQL select statement?
Or, are they mutually exclusive??

What I want to be able to do is group results and then order them. For
instance, I'd like to be able to "GROUP BY jobtype ORDER BY salary"...

This would put all the accountants together, sorted by their salary...
... put all the programmers together, sorted by their salary...
Etc...

When I use both group by and order by together in a select statement...
I get NO results returned... but I also get no database error.

Thanks for any assistance,
Paul K

---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>



---
Subscribe to the digest:
<mailto:<email address removed>>
Unsubscribe:
<mailto:<email address removed>>