Solved

grouping not working in sql

Posted on 2008-10-28
4
225 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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. …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

634 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