Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

grouping not working in sql

Posted on 2008-10-28
4
Medium Priority
?
233 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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

660 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