Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

need help with group by query

5 posts in General Discussion Last posting was on 2011-04-07 08:52:18.0Z
Mark1110 Posted on 2011-04-06 19:56:39.0Z
Sender: 323d.4d9cc33e.1804289383@sybase.com
From: Mark1110
Newsgroups: sybase.public.ase.general
Subject: need help with group by query
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d9cc577.326d.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 6 Apr 2011 12:56:39 -0700
X-Trace: forums-1-dub 1302119799 10.22.241.41 (6 Apr 2011 12:56:39 -0700)
X-Original-Trace: 6 Apr 2011 12:56:39 -0700, 10.22.241.41
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30094
Article PK: 79324

have two tables I need to run a query on. The first is has a
company code and code order, the second has a the customer
information. I need to run a query that just list the
minimum code order for a customer based on the company code.
There can be several rows based on the customer id. I just
need one row returned that is based on the code order. For
example

code order customer id company code
--------------------------------------
9 0011 "IL"
12 0011 "VA"
6 0011 "ME"

The only row I want returned is the last row since it has
the minimum code order. I tried this:

SELECT a.code_order, b.customer_id, b.company_cd
FROM table1 a, table2 b
WHERE a.customer_id = b.customer_id
GROUP BY b.customer_id
HAVING min(a.code_order) = a.code_order

but that didn't work. Is there another way of doing this.


"Paul Horan[Sybase]" Posted on 2011-04-06 21:24:46.0Z
From: "Paul Horan[Sybase]" <phoran AT sybase DOT com>
Newsgroups: sybase.public.ase.general
References: <4d9cc577.326d.1681692777@sybase.com>
Subject: Re: need help with group by query
Lines: 40
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d9cda1e$1@forums-1-dub>
Date: 6 Apr 2011 14:24:46 -0700
X-Trace: forums-1-dub 1302125086 10.22.241.152 (6 Apr 2011 14:24:46 -0700)
X-Original-Trace: 6 Apr 2011 14:24:46 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30095
Article PK: 79323

Select a.code_order, b.customer_id, b.company_cd
from table1 a
join table2 b
on a.customer_id = b.customer_id
where a.code_order =
(Select min( code_order )
from table1 c
where c.customer_id = a.customer_id ) ;

--
Paul Horan[Sybase]
http://paulhoran.ulitzer.com

<Mark1110> wrote in message news:4d9cc577.326d.1681692777@sybase.com...
> have two tables I need to run a query on. The first is has a
> company code and code order, the second has a the customer
> information. I need to run a query that just list the
> minimum code order for a customer based on the company code.
> There can be several rows based on the customer id. I just
> need one row returned that is based on the code order. For
> example
>
> code order customer id company code
> --------------------------------------
> 9 0011 "IL"
> 12 0011 "VA"
> 6 0011 "ME"
>
> The only row I want returned is the last row since it has
> the minimum code order. I tried this:
>
> SELECT a.code_order, b.customer_id, b.company_cd
> FROM table1 a, table2 b
> WHERE a.customer_id = b.customer_id
> GROUP BY b.customer_id
> HAVING min(a.code_order) = a.code_order
>
> but that didn't work. Is there another way of doing this.


"Mark A. Parsons" <iron_horse Posted on 2011-04-06 21:27:07.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: need help with group by query
References: <4d9cc577.326d.1681692777@sybase.com>
In-Reply-To: <4d9cc577.326d.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d9cdaab$1@forums-1-dub>
Date: 6 Apr 2011 14:27:07 -0700
X-Trace: forums-1-dub 1302125227 10.22.241.152 (6 Apr 2011 14:27:07 -0700)
X-Original-Trace: 6 Apr 2011 14:27:07 -0700, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30096
Article PK: 79325

You'll need to supply the definitions for both tables.

Also helpful would be a sample set of data from both tables along with what you expect as the final result.

Also, you claim your query didn't work ... what did it generate and what did you expect?

On 04/06/2011 15:56, Mark1110 wrote:
> have two tables I need to run a query on. The first is has a
> company code and code order, the second has a the customer
> information. I need to run a query that just list the
> minimum code order for a customer based on the company code.
> There can be several rows based on the customer id. I just
> need one row returned that is based on the code order. For
> example
>
> code order customer id company code
> --------------------------------------
> 9 0011 "IL"
> 12 0011 "VA"
> 6 0011 "ME"
>
> The only row I want returned is the last row since it has
> the minimum code order. I tried this:
>
> SELECT a.code_order, b.customer_id, b.company_cd
> FROM table1 a, table2 b
> WHERE a.customer_id = b.customer_id
> GROUP BY b.customer_id
> HAVING min(a.code_order) = a.code_order
>
> but that didn't work. Is there another way of doing this.


Debapriyo Bhattacharya , Sybase Certified Developer Associate Posted on 2011-04-07 07:39:04.0Z
Sender: 47f6.4d9d6290.1804289383@sybase.com
From: Debapriyo Bhattacharya , Sybase Certified Developer Associate
Newsgroups: sybase.public.ase.general
Subject: Re: need help with group by query
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d9d6a18.490c.1681692777@sybase.com>
References: <4d9cdaab$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Apr 2011 00:39:04 -0700
X-Trace: forums-1-dub 1302161944 10.22.241.41 (7 Apr 2011 00:39:04 -0700)
X-Original-Trace: 7 Apr 2011 00:39:04 -0700, 10.22.241.41
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30097
Article PK: 79326


> You'll need to supply the definitions for both tables.
>
> Also helpful would be a sample set of data from both
> tables along with what you expect as the final result.
>
> Also, you claim your query didn't work ... what did it
> generate and what did you expect?
>
> On 04/06/2011 15:56, Mark1110 wrote:
> > have two tables I need to run a query on. The first is
> > has a company code and code order, the second has a the
> > customer information. I need to run a query that just
> > list the minimum code order for a customer based on the
> > company code. There can be several rows based on the
> > customer id. I just need one row returned that is based
> > on the code order. For example
> >
> > code order customer id company code
> > --------------------------------------
> > 9 0011 "IL"
> > 12 0011 "VA"
> > 6 0011 "ME"
> >
> > The only row I want returned is the last row since it
> > has the minimum code order. I tried this:
> >
> > SELECT a.code_order, b.customer_id, b.company_cd
> > FROM table1 a, table2 b
> > WHERE a.customer_id = b.customer_id
> > GROUP BY b.customer_id
> > HAVING min(a.code_order) = a.code_order
> >
> > but that didn't work. Is there another way of doing
> this.

Try this one:

select * from table1
where code_order in (select min(code_order) from #temp1
group by customer_id)


Debapriyo Bhattacharya , Sybase Certified Developer Associate Posted on 2011-04-07 08:52:18.0Z
Sender: 47f6.4d9d6290.1804289383@sybase.com
From: Debapriyo Bhattacharya , Sybase Certified Developer Associate
Newsgroups: sybase.public.ase.general
Subject: Re: need help with group by query
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d9d7b41.4b0f.1681692777@sybase.com>
References: <4d9d6a18.490c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Apr 2011 01:52:18 -0700
X-Trace: forums-1-dub 1302166338 10.22.241.41 (7 Apr 2011 01:52:18 -0700)
X-Original-Trace: 7 Apr 2011 01:52:18 -0700, 10.22.241.41
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30098
Article PK: 79327


> > You'll need to supply the definitions for both tables.
> >
> > Also helpful would be a sample set of data from both
> > tables along with what you expect as the final result.
> >
> > Also, you claim your query didn't work ... what did it
> > generate and what did you expect?
> >
> > On 04/06/2011 15:56, Mark1110 wrote:
> > > have two tables I need to run a query on. The first is
> > > has a company code and code order, the second has a
> > > the customer information. I need to run a query that
> > > just list the minimum code order for a customer based
> > > on the company code. There can be several rows based
> > > on the customer id. I just need one row returned that
> > > is based on the code order. For example
> > >
> > > code order customer id company code
> > > --------------------------------------
> > > 9 0011 "IL"
> > > 12 0011 "VA"
> > > 6 0011 "ME"
> > >
> > > The only row I want returned is the last row since it
> > > has the minimum code order. I tried this:
> > >
> > > SELECT a.code_order, b.customer_id, b.company_cd
> > > FROM table1 a, table2 b
> > > WHERE a.customer_id = b.customer_id
> > > GROUP BY b.customer_id
> > > HAVING min(a.code_order) = a.code_order
> > >
> > > but that didn't work. Is there another way of doing
> > this.
>
>

Sorry for the typos before. Below is the corrected query:
Try this one:

select * from table1 a
where code_order in (select min(code_order) from table1 b
group by customer_id having a.customer_id = b.customer_id)