Solved

grouping not working in sql

Posted on 2008-10-28
4
211 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 250 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 250 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
View SQL 2005 Job package 16 44
SQL Help 27 46
date diff with Fiscal Calendar 4 32
Need help with a Stored Proc on Sql Server 2012 4 11
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

823 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