December 26, 2008 at 9:08 am
Hi,
I have table with 5 columns.
In that column has a date where I have dates from january 2007.
If I import data to table in sql 2005 adatabase I can't make max(date).
What I exactly need?
I would like to get only one column whre I will see maximum date with other columns data.
It should be:
RN DATE COUNT DAYTYPE
23973825.12.08 0:0064ThursdayMOBIL
If I make
SELECT RN, MAX(DATE) , COUNT, DAY, TYPE
FROM name of table
GROUP BY RN, COUNT, DAY, TYPE
I can't get only one row!!!
Always I get all rows. I did it with management studio.
Somewhere is mistake and I really don't know what to do.
I search internet anf found select in select but it doesn't help me or I didn't know how to get it.
Any suggestion?
December 26, 2008 at 9:22 am
as I can see you just select the data with date ordering desc and you will find the max value of date and the first row is what you are looking!
December 26, 2008 at 9:27 am
AHA you want to display just one record...ok you can do like this:
SELECT TOP 1 * FROM your_table
ORDER BY DATE DESC
December 26, 2008 at 9:49 am
Are you trying to select the detail for whichever row has the max date? If so, you first need a CTE or subquery that does a summary query to identify only those rn/date combinations. You then join the detail table to that result, like so:
declare @sample table (rn int, [date] datetime, count_mobile int, report_type_code varchar(20))
insert into @sample
select 239738,'12/21/08', 105, 'Shell' union all
select 239738,'12/23/08', 31, 'BP' union all
select 239738,'12/25/08', 64, 'Mobil'
;with cteMaxDates as
(select rn,MAX(date) as maxDate
from @sample
group by rn
)
select s.rn,s.date,s.count_mobile as [count],s.report_type_code as [type],DATENAME(dw,[date]) as [day]
from @sample s
join cteMaxDates c on c.rn = s.rn and c.maxDate = s.[date]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 26, 2008 at 12:04 pm
I just need command in order to get max report date in only one row.
It should look like this:
RN DATE COUNT DAY TYPE
239738 25.12.08 0:00 64 Thursday MOBIL
I just need one row with all this columns.
In attachment you can find a table. This excel file is dta source, move data to destination table and try to get all columns with one row with some ????? command which will give max report date.
Thats all. I hope that u understand me. Thanks.
December 26, 2008 at 12:16 pm
Thank you! Just simple idea. I did it.
select top1*
from "table name"
order by date desc
Hvala!Thanks!
December 26, 2008 at 2:08 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply