SQL Query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query

Author

Topic

AverageJoe
Starting Member

2 Posts

Posted - 2007-10-18 : 12:39:37

Hi,

Can someone help. I have the following data

id nkTrans id_Date Amount
1 LE1 2006-12-01 5
2 LE1 2007-03-16 10
3 LE1 2007-09-14 25
4 LE2 2006-12-12 13
5 LE2 2007-09-12 15
6 LE2 2007-10-10 17

I want a query to return rows 1 and 4. this is grouped by nkTrans with the lowest id_Date.

Can anyone help?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-18 : 12:42:25

have you tried the query? You seem to have got it in words.. "grouped by nkTrans with the lowest id_Date"

use MIN(id_Date) to get the lowest id_date.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tm
Posting Yak Master

160 Posts

Posted - 2007-10-18 : 13:51:49

AverageJoe's questions does not make sense but if he wants to return rows 1 and 4 you would need to based the group by year and month as the day does not match.

Row 1 id_date = 2006-12-01
Row 4 id_date = 2006-12-12

Something like this maybe ..

select * from table1
where year(id_date) = (select year(min(id_date)) from table1)
and month(id_date) = (select month(min(id_date)) from table1)

AverageJoe
Starting Member

2 Posts

Posted - 2007-10-18 : 14:23:25

Why doesn't this question makes sense. I want the rows with the lowest dates returned for each nkTrans

I can't do it by:

Select id,nkTrans,min(id_Date),Amount
From Table1
Group by nkTrans

This query will not work, as I will need to group by all columns therefore having the same selection as I started with!?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-18 : 14:27:53

[code]
Select T1.*
From YourTable T1
Join (
Select nkTrans,min(id_Date) MinDate
From YourTable
Group by nkTrans
) T2 on T1.nkTrans = T2.nkTrans And T1.id_Date = T2.MinDate
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tm
Posting Yak Master

160 Posts

Posted - 2007-10-18 : 16:09:10

My apologies .. AverageJoe. Misread what you needed,
Thanks Dinakar for putting it correct.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources