• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

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

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?


0
sonetinc
Asked:
sonetinc
  • 5
  • 2
  • 2
  • +3
2 Solutions
 
reb73Commented:
You are missing the following line -

GROUP BY ProgramNo
0
 
reb73Commented:
Or try -

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

With TOP it would be

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

/peter
0
Independent Software Vendors: 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!

 
BrandonGalderisiCommented:
select * from YourTaable where startdate = (select max(startdate) from YourTable)
0
 
sunithnairCommented:
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

0
 
Mark WillsTopic AdvisorCommented:
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)
0
 
BrandonGalderisiCommented:
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."

0
 
sonetincAuthor 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?

0
 
Mark WillsTopic AdvisorCommented:
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...
0
 
Mark WillsTopic AdvisorCommented:
replace the limit 1 with top 1 as per pivar
0
 
pivarCommented:
Have you tried my posting 23684478?

SELECT TOP 1 StartDate, ProgramNo FROM MyTable ORDER BY StartDate DESC
0
 
Mark WillsTopic AdvisorCommented:
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 ?
0
 
sonetincAuthor Commented:
Correct, the query returns two rows. Either way I have to add "TOP 1".
0
 
Mark WillsTopic AdvisorCommented:
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...
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 5
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now