Group By other columns in output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By other columns in output

Author

Topic

Nimay11
Starting Member

3 Posts

Posted - 2010-06-02 : 22:47:19

Hi All,

I have always been rejecting SQL whereever I can use C#, but....

TableData

Id1 Cake1 03/03/2010
Id2 Cake1 01/03/2010
Id3 Cake2 03/03/2010
Id4 Cake2 01/03/2010

The Output should be (Latest Distinct cakes)
Id1 Cake1 03/03/2010
Id3 Cake2 03/03/2010

What I have got is

select distinct cake, max(date) from table
group by cake

or

select distinct cake,(select max (date) from tablef1 where f1.cake= f2.cake) from table f2

The above two queries gives me output as
Cake1 03/03/2010
Cake2 03/03/2010

But I want the Id column in the output as well. How can I do that?

Thanks,

SQL Learner

nirene
Yak Posting Veteran

98 Posts

Posted - 2010-06-03 : 01:25:55

Add Max(Id) or Min(Id) to your query

Select ~~distinct~~ cake, max(date) from table group by cake

When you use Group By, Distinct is not necessary

Nirene

Nimay11
Starting Member

3 Posts

Posted - 2010-06-03 : 01:39:40

Hi There,

Modifying the query as you suggested didn't work. It no doubt shown the Id column but didn't determine the correct one.

Thanks,

SQL Learner

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-03 : 03:12:39

Try this -


Create table temp ( id varchar(10), CakeName varchar(10), date datetime)
Insert into temp
SELECT 'Id1','Cake1','03/03/2010'
UNION ALL
SELECT 'Id2','Cake1','01/03/2010'
UNION ALL
SELECT 'Id3','Cake2','03/03/2010'
UNION ALL
SELECT 'Id4','Cake2','01/03/2010'

select Distinct id, Cakename, t.date From temp t
CROSS APPLY
(
SELECT MAX(Date) date FROM temp
) a
WHERE t.date = a.date

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

GopiMuluka
Starting Member

12 Posts

Posted - 2010-06-03 : 03:26:59

Cross Apply query fails as it only considers Max Date in entire table
Check this

Create table temp ( id varchar(10), CakeName varchar(10), date datetime)
Insert into temp
SELECT 'Id1','Cake1','03/03/2010'
UNION ALL
SELECT 'Id2','Cake1','01/03/2010'
UNION ALL
SELECT 'Id3','Cake2','01/03/2010'
UNION ALL
SELECT 'Id4','Cake2','04/03/2010'

SELECT B.* FROM
(SELECT CakeName, MAX(date) MaxDate from temp
GROUP BY CakeName) A
INNER JOIN temp B
ON A.CakeName=B.CakeName
AND A.MaxDate=B.Date
ORDER BY B.CakeName

drop table temp

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-03 : 05:57:22

But this is what the OP's requirement...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources