Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

grouping not working in sql

Posted on 2008-10-28
4
Medium Priority
?
241 Views
Last Modified: 2011-10-03
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

0
Comment
Question by:Lucia
4 Comments
 
LVL 4

Expert Comment

by:GO-87
ID: 22822066
Have you tried
SELECT TOP 1
     EFFTD, CRSE_ID, DESCR
FROM Table1
 ORDER BY EFFTD DESC
0
 
LVL 9

Accepted Solution

by:
jamesgu earned 1000 total points
ID: 22822275
the DESCR of those rows are different i guess even though they look alike,

you may want to check that
0
 
LVL 3

Assisted Solution

by:richard_crist
richard_crist earned 1000 total points
ID: 22822405
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
 

Author Closing Comment

by:Lucia
ID: 31510742
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question