October 31, 2005 at 12:27 am
I have a table xyz with fields co_id (varchar) and date
data eg:
101 10/01/2005
101 10/02/2005
101 10/03/2005 =>
102 10/01/2005
102 10/02/2005 =>
103 10/01/2005
103 10/02/2005 =>
Want to display the last days record (the marked one only) for each id ???
October 31, 2005 at 12:42 am
Select data, max(datecol) as datecol from dbo.YourTable group by data
October 31, 2005 at 5:07 am
if more than two fields then how can i group
i tried with the below query it giving cartition product result
"Select data1,data2, max(datecol) as datecol from dbo.YourTable group by data1.data2"
October 31, 2005 at 12:13 pm
When you use Group By Clause
make sure to place all columns used in
Select Query.
In your Case column in Group By need to be separated by , not Period [.]
i.e Group by Data1, Data2
Select data1,data2, max(datecol) as datecol from dbo.YourTable group by data1,data2
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
October 31, 2005 at 11:50 pm
Your query is working but i cannot display the other fields of the grouped items.
Table Structure
Table name: Stock
Co_id char
open float
High float
Low float
Close float
date1 date
Eg:
Co_id date1 Open High Low
101 10/01/2005 101 102 102
101 10/02/2005 201 202 203
101 10/03/2005 105 105 105 =>
102 10/01/2005 402 402 402
102 10/02/2005 201 205 201 =>
103 10/01/2005 504 505 501
103 10/02/2005 1001 1002 1001 =>
I Want to display the last days record (the marked => only) for each id ???
October 31, 2005 at 11:51 pm
Your query is working but i cannot display the other fields of the grouped items.
Table Structure
Table name: Stock
Co_id char
open float
High float
Low float
Close float
date1 date
Eg:
Co_id date1 Open High Low
101 10/01/2005 101 102 102
101 10/02/2005 201 202 203
101 10/03/2005 105 105 105 =>
102 10/01/2005 402 402 402
102 10/02/2005 201 205 201 =>
103 10/01/2005 504 505 501
103 10/02/2005 1001 1002 1001 =>
I Want to display the last days record (the marked => only) for each id ???
October 31, 2005 at 11:55 pm
Your query is working but i cannot display the other fields of the grouped items.
Table Structure
Table name: Stock
Co_id char
open float
High float
Low float
Close float
date1 date
Eg:
Co_id date1 Open High Low
101 10/01/2005 101 102 102
101 10/02/2005 201 202 203
101 10/03/2005 105 105 105 =>
102 10/01/2005 402 402 402
102 10/02/2005 201 205 201 =>
103 10/01/2005 504 505 501
103 10/02/2005 1001 1002 1001 =>
I Want to display the last days record (the marked => only) for each id ???
November 1, 2005 at 7:57 am
select stock.co_id,stock.date1,stock.[open],stock.high,stock.low
from dbo.stock stock
inner join /*only matches*/
(
select co_id,max(date1) as lastdate
from dbo.stock
group by co_id
) As stocklastdate /*subquery to select on last date for each id*/
on stock.co_id=stocklastdate.co_id
and stock.date1=stocklastdate.lastdate
order by stock.co_id
November 1, 2005 at 12:02 pm
Saiju,
I came out with exactly the same query that Jo Patyn has suggested. Now as it is already posted I am not posting duplication of same query again.
Still I would like to add 2 comment to this as
1. In your example you have used Co_id as char
which is not good choice better use int .
2. Make sure you do not insert 2 identical date1
For same co-id. In that case bettter choice is
to have primary Key as composite one on
Coid+date1+Any one additional Column or
you can add one column with identity and make primary key as IdenCol+Coid+date1.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
November 2, 2005 at 1:05 am
Thank you Mr. Jo Patyn and Mr. Sameer Raval for your information. It worked...
Thank You very much
Saiju
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply