sonetinc
asked on
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?
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?
Or try -
SELECT * FROM MyTable WHERE ID = (SELECT MAX(ID) FROM MyTable)
SELECT * FROM MyTable WHERE ID = (SELECT MAX(ID) FROM MyTable)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is this what you are looking for as reb73 mentioned in the 1st post?
SELECT MAX (StartDate), ProgramNo
FROM MyTable group by ProgramNo
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)
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."
"I am looking for the program no. with the most recent starting date."
ASKER
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?
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?
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...
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...
replace the limit 1 with top 1 as per pivar
Have you tried my posting 23684478?
SELECT TOP 1 StartDate, ProgramNo FROM MyTable ORDER BY StartDate DESC
SELECT TOP 1 StartDate, ProgramNo FROM MyTable ORDER BY StartDate DESC
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 ?
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 ?
ASKER
Correct, the query returns two rows. Either way I have to add "TOP 1".
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...
GROUP BY ProgramNo