GROUP BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

GROUP BY

Author

Topic

CosmicGirl
Starting Member

9 Posts

Posted - 2006-01-31 : 19:23:25

Hi all,

i am trying to build a query:

SELECT al_pl_description, al_sv_id, al_sv_subservice, al_out_id, al_pl_selector,al_pb_id, MAX(al_pl_reg_start_date)
FROM al_pricelist

WHERE
((@service is NULL) OR (al_sv_id = @service)) AND
((@subservice is NULL) OR (al_sv_subservice = @subservice)) AND
((@output is NULL) OR (al_out_id = @output)) AND
((@selector is NULL) OR (al_pl_selector = @selector)) AND
((@pricebreak is NULL) OR (al_pb_id = @pricebreak)) AND
(al_pl_promo_num is NULL)

GROUP BY al_sv_id, al_sv_subservice, al_out_id, al_pl_selector, al_pb_id

and i get the following error:
"Column 'al_pricelist.al_pl_description' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

i need to select al_pl_description field, and i am not sure why it is not letting me.

what i am trying to do is select all the rows that fit the WHERE clause. However, among the rows there may be duplicate rows with the only field that differs being start_date...which is why i am trying to fetch the latest.

PLEASE HELP!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-31 : 19:33:44

put the al_pl_description in your GROUP BY

----------------------------------
'KH'

CosmicGirl
Starting Member

9 Posts

Posted - 2006-01-31 : 19:38:48

quote:
Originally posted by khtan

put the al_pl_description in your GROUP BY

----------------------------------
'KH'

i have other 11 fields besides al_pl_description that I need to select. Putting all 11 in GROUP BY would be kinda weird. I am not even sure that i am doing a correct query for what i need...HELP

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-31 : 19:56:10

you are most likely not grouping correctly, or you are doing it in the wrong place.

see: http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

that should give you some ideas about how to restructure your select.

BUt if everything is really in 1 big (presumable unnormalized table) then you will probably have to GROUP BY all of the columns you wish to return other than the MAX(). That's just the way it works, I'm afraid.

CosmicGirl
Starting Member

9 Posts

Posted - 2006-01-31 : 20:17:01

thanks.
the table does not contain the field ID, which makes it impossible to follow your example. Unfortunately, i am not in the liberty to change the table structure, so the only way is to GROUP ON all the fields i want returned? please confirm

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-31 : 20:18:40

quote:
what i am trying to do is select all the rows that fit the WHERE clause. However, among the rows there may be duplicate rows with the only field that differs being start_date...which is why i am trying to fetch the latest.

If I understand you correctly . . .

select * 
from   al_pricelist
where  al_pl_reg_start_date = (select MAX(al_pl_reg_start_date)
                                   from  al_pricelist
                                   where ...
                               )
and    ...


select TOP 1 *
from   al_pricelist
where  ...
order by al_pl_reg_start_date DESC

----------------------------------
'KH'

CosmicGirl
Starting Member

9 Posts

Posted - 2006-01-31 : 20:30:04

2khtan:
i dont think that i have explained myself clearly:)
each row in the table has 5 criteria (the ones that i group by).

the user however, can search based on 1 .. 5 criteria. The rows that get returned have to be the ones with the latest date for this particular set of 5 criteria.....
your second query i believe will only return 1 result...

thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-31 : 21:47:11

Does this table have a primary key?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-31 : 23:18:53

Maybe it will be easier to understand if you post the table structure (DDL), some sample data and expected result

----------------------------------
'KH'

CosmicGirl
Starting Member

9 Posts

Posted - 2006-02-01 : 13:08:18

ok, fair enough:

say i have the following fields in the table:

characteristic1, characteristic2, characteristic3, characteristic4, characteristic5, field1, field2, field3,..., field10, start_date

now on my web page I have a form that lets a user to search the table (just that one table), based on the 5 charateristics (see field list: characteristic1....characteristic5).

the user can also search based on less than 5 in which case they will get multiple results.
what i am trying to do is in the case above, not show ALL results returned - within the rows that possess the same 5 characteristics, i would like to ONLY show 1 row - the ones with the latest start_date.

the little catch is though that i still need to SELECT other fields ...and when i did the GROUP BY characteristic1, ..., characteristic5 I can not do SELECT * without grouping on ALL fields in the table.

thanks in advance:)

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-01 : 13:23:20

Follow the suggestions in the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

CosmicGirl
Starting Member

9 Posts

Posted - 2006-02-01 : 13:24:08

In other words - for the unique set of 5 characteristics I need to show ONLY 1 row - the one with the latest date

CosmicGirl
Starting Member

9 Posts

Posted - 2006-02-01 : 14:09:02

1) State the Q:
For the unique set of 5 characteristics I need to show ONLY 1 row - the one with the latest date.

2) DDL:(very big)
CREATE TABLE [dbo].[AL_PRICELIST] (
[al_sv_id] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[al_sv_subservice] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[al_out_id] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[al_pb_id] [int] NOT NULL ,
[al_pl_selector] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[al_pl_print_id_barcode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[al_pl_description] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[al_pl_upc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[al_pl_upc_type] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[al_pl_zero_qty_upc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[al_pl_reg_start_date] [datetime] NULL ,
[al_pl_reg_price] [money] NOT NULL ,
[al_pl_price] [money] NULL ,
[al_pl_cost] [money] NULL ,
[al_pl_promo_num] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[al_pl_promo_start_date] [datetime] NULL ,
[al_pl_promo_end_date] [datetime] NULL ,
[al_store_id] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[al_pl_tax_status] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[al_pl_time_start] [datetime] NULL ,
[al_pl_short_desc] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[al_pl_keychain_price] [money] NULL
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [AL_PRICELIST_PK] ON [dbo].[AL_PRICELIST]([al_sv_id], [al_sv_subservice], [al_out_id], [al_pb_id], [al_pl_selector], [al_pl_price], [al_pl_promo_start_date], [al_pl_promo_end_date]) ON [PRIMARY]
GO

Expected results:
for a unique set of: al_sv_id, al_sv_subservice, al_out_id, al_pb_id and al_pl_selector I would like ONLY one row fetched the one with the latest al_pl_reg_start_date.

for the attempted query please see the beginning of this topic

CosmicGirl
Starting Member

9 Posts

Posted - 2006-02-01 : 18:19:44

nevermind, i got it

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-01 : 20:49:10

quote:
Hint: Want your questions answered fast? Follow the direction in this link

Brett's suggestion is very effective. Most of the time poster will found the solution to their problem after following the link http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

----------------------------------
'KH'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-01 : 22:20:32

quote:
Originally posted by CosmicGirl
CREATE UNIQUE INDEX [AL_PRICELIST_PK] ON [dbo].[AL_PRICELIST]([al_sv_id], [al_sv_subservice], [al_out_id], [al_pb_id], [al_pl_selector], [al_pl_price], [al_pl_promo_start_date], [al_pl_promo_end_date]) ON [PRIMARY]
GO

A numerical "Price" column is part of your primary key? I'm all for composite PK's, but I doubt this is really what you want.

Kristen
Test

22859 Posts

Posted - 2006-02-02 : 02:10:23

"Brett's suggestion is very effective"

I think SQL Team should sell a cardboard cutout of a fellow DBA.

Half the time I type a question into SQL Team I've figured out the answer by the time I've finished typing the question ... talking to a cardboard dummy would probably be as good ...

Kristen

CosmicGirl
Starting Member

9 Posts

Posted - 2006-02-02 : 12:33:42

2Khan: I have figured it out using other sources than Brett's. Brett's just teaches you how to lay out your question. The forums are just too slow:)

2jsmith: the table and the keys were not created by me, plus in this case price should be included in the composite key since we want to prevent entering a line with the same characteristics and same price.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources