grouping not working in sql

Good Morning,

I have run a query where I expect to pick up one record only from a grouping. Ie the most current description.  This is not happenning.

I have attached the code snippet which includes result.

Any assistance would be greatly appreciated.

Sincerely,
Lucy
This is my query:
SELECT  MAX(Table1.EFFTD),
   Table1.CRSE_ID ,
   Table1.DESCR
FROM Table1
GROUP BY 
   Table1.CRSE_ID, 
   Table1.DESCR, 
    (Table1.EFFTD)
 
 
result:
fields
Expr1000	                    CRSE_ID	                                     DESCR
2001-01-01 00:00:00.000	1017	This course presents a basic introduction to accounting, focusing on the steps in the accounting cycle. Emphasis is placed on understanding the preparation of financial statements. Consideration is also given to accounting for expenses, fixed assets, inve
 
2003-05-05 00:00:00.000	1017	This course presents a basic introduction to accounting, focusing on the steps in the accounting cycle. Emphasis is placed on understanding the preparation of financial statements. Consideration is also given to accounting for expenses, fixed assets, inve

Open in new window

BrockAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GO-87Commented:
Have you tried
SELECT TOP 1
     EFFTD, CRSE_ID, DESCR
FROM Table1
 ORDER BY EFFTD DESC
0
jamesguCommented:
the DESCR of those rows are different i guess even though they look alike,

you may want to check that
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
richard_cristCommented:
Your query contains the date in the grouping.  If you want only one line per CRSE_ID and DESCR combination, then  the query should be as follows:

SELECT  MAX(Table1.EFFTD),
   Table1.CRSE_ID ,
   Table1.DESCR
FROM Table1
GROUP BY
   Table1.CRSE_ID,
   Table1.DESCR

The above modified version will return one distinct row for each CRSE_ID and DESCR combination, and the select will show the maximum EFFTD for that grouping.

If you include in your group by a column with which you are also using an aggregate function in your column selection, then you will get distinct rows for each distinct date.  You can include in your select portion any columns that are not part of the grouping as long as they are in aggregate functions.

Let me know if you need more information about this.
0
BrockAuthor Commented:
1. This is the code, I did use. And I did have situatuions where the description was the same which was causing the havoc.

Thanks to all of you,
Lucy

SELECT
A.CRSE_ID ,
CAST(A.DESCRLONG AS NCHAR(255)),
COURSE_TITLE_LONG,
UNITS_MAXIMUM
FROM PS_CRSE_CATALOG A
WHERE A.EFFDT IN
(SELECT MAX(A1.EFFDT)
FROM PS_CRSE_CATALOG A1 WHERE A.CRSE_ID=A1.CRSE_ID)
--AND A.CRSE_ID=1582
GROUP BY
A.CRSE_ID,
CAST(A.DESCRLONG AS NCHAR(255)),
COURSE_TITLE_LONG,
UNITS_MAXIMUM
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.