We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Column ID in T-SQL from a query with an aggregate function

Medium Priority
424 Views
Last Modified: 2012-05-06
I am trying to retrieve a column value in T-SQL from a query with an aggregate function:
I am looking for the program no. with the most recent starting date.

I am working with this table (simplified):

ID | ProgramNo | StartDate | Course
1 | 10 | 2009-01-01 | CIS101
2 | 11 | 2009-02-01 | CIS102
3 | 12 | 2009-03-01 | CIS103
4 | 12 | 2009-03-01 | ACCT101

SELECT MAX (StartDate), ProgramNo
FROM MyTable

This is throwing out an error: ProgramNo is not part of the aggregate function or a GROUP BY clause.

I have also looked into the TOP function, with the same result.

Both of them complain that no other column can be part of the query.

How is it possible to get the program no in an efficient way?


Comment
Watch Question

Commented:
You are missing the following line -

GROUP BY ProgramNo

Commented:
Or try -

SELECT * FROM MyTable WHERE ID = (SELECT MAX(ID) FROM MyTable)
Commented:
Hi,

With TOP it would be

SELECT TOP 1 StartDate, ProgramNo FROM MyTable ORDER BY StartDate DESC

/peter

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
select * from YourTaable where startdate = (select max(startdate) from YourTable)
Is this what you are looking for as reb73 mentioned in the 1st post?
SELECT MAX (StartDate), ProgramNo
FROM MyTable group by ProgramNo

Open in new window

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Yep, reb73 got it first up - just missing the group by....  Thhings like MAX() and SUM() and COUNT() are aggregate functions, meaning they are somehow combined into a new value for nominated columns. Anything in the select that is not and aggregat function must be part of a group by.


if wanting all the details of that row, then you can do things like :


select * from mytable
where id = (select id from mytable order by date desc)

or by programNo

select * from mytable
where id = (select id from mytable M where M.programno = mytable.programno order by date desc)
my post http:#//a23684682 lists the program no for the most recent startdate.  Not the most recent start date for each product no.

"I am looking for the program no. with the most recent starting date."

Author

Commented:
Thanks for the quick response.

I am looking for the most recent ProgramNo (only one row should be returned). I tried the GROUP BY CLAUSE before.

I need to run the MAX aggregate function on the date column, which returned only the ProgramNo of the most recent program.

reb73's SELECT * FROM MyTable WHERE ID = (SELECT MAX(ID) FROM MyTable) gives me the highest ID.

I am looking for the ID of the most recent program.

In MySQL I would do it in the following way:

SELECT *
FROM MyTable
ORDER BY StartDate DESC
LIMIT 1;

How can this be accomplished in T-SQL?

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
too true in so much as reb73 did supply by program, but the one that gives the correct answer first is pivar , and yours will return multiple rows... and mine should have been :

select * from mytable
where id = (select top 1 id from mytable order by date desc)

or by programNo

select * from mytable
where id = (select top 1 id from mytable M where M.programno = mytable.programno order by date desc)

By the way, congrats on the genius...
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
replace the limit 1 with top 1 as per pivar

Commented:
Have you tried my posting 23684478?

SELECT TOP 1 StartDate, ProgramNo FROM MyTable ORDER BY StartDate DESC
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
And sonetinc, hate to say it but brandon's routine does not work, but gets the assist - most interesting...

1 | 10 | 2009-01-01 | CIS101
2 | 11 | 2009-02-01 | CIS102
3 | 12 | 2009-03-01 | CIS103
4 | 12 | 2009-03-01 | ACCT101

select max(startdate) = 2009-03-01

select * from yourtable where startdate = 2009-03-01 returns two rows - does it not ?

Author

Commented:
Correct, the query returns two rows. Either way I have to add "TOP 1".
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
my second also gave the correct results (and understand that I clearly do not deserve points other than pointing out the top 1 in place of limit 1)... and the assist goes to... the one that doesn't work... pivar was slighted, and deserved the full deal...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.