Sql Query issue - 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

General SQL Server Forums

New to SQL Server Programming

Sql Query issue

Author

Topic

roma_victa
Starting Member

4 Posts

Posted - 2011-06-28 : 23:43:37

hi all,

i have a table which

Date1 UsedLic

2011-06-19 70
2011-06-19 76
2011-06-19 81
2011-06-19 86
2011-06-19 87
2011-06-19 4
2011-06-19 4
2011-06-19 4
2011-06-19 4
2011-06-20 43
2011-06-20 44
2011-06-20 45
2011-06-20 98
2011-06-20 45
2011-06-20 96
2011-06-20 96
2011-06-20 98
2011-06-20 45
2011-06-20 99
2011-06-20 100
see my query

select

top 20 CONVERT(VARCHAR(10), [DateTime], 126) as Date1, max(UsedLic) as UsedLic from TBBLOG_ALL where [AppName] =

'65100BLDSYS_F'

group by [DateTime]

?which gives me the above result . what i want is that i need only one record per day with the higest "UsedLic "

?in the above result the record for

?2011-06-19 70
2011-06-19 76
2011-06-19 81
2011-06-19 86
2011-06-19 87 is the higest

the output should me

2011-06-19 87 ?

2011-06-20 100

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-06-29 : 01:57:48

Which version of mssql you are using
This works on MS SQL 2005 Or greater
Declare @t table(date1 datetime,usedlic int)
insert into @t
Select '2011-06-19', 70 union all
Select '2011-06-19', 76 union all
Select '2011-06-19', 87 Union all
Select '2011-06-19', 4 union all
Select '2011-06-20', 43 Union all
Select '2011-06-20', 100

;with cte as (
Select *,row=row_number() over (partition by date1 order by usedlic desc) from @t)
Select * from cte where row=1

In Love... With Me!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-29 : 01:58:22

[code]
select top 20
~~CONVERT(VARCHAR(10), [DateTime], 126) as Date1,~~
dateadd(day, datediff(day, 0, [DateTime]), 0) as Date1,
max(UsedLic) as UsedLic
from TBBLOG_ALL
where [AppName] = '65100BLDSYS_F'
group by dateadd(day, datediff(day, 0, [DateTime]), 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

roma_victa
Starting Member

4 Posts

Posted - 2011-06-29 : 07:50:51

i tried the above but i have get the error below

Column 'TBBLOG_ALL.DateTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

roma_victa
Starting Member

4 Posts

Posted - 2011-06-30 : 17:08:56

here is some sample data

ServerName DateTime AppName MaxLic UsedLic
AUHBRPS01 2011-06-20 09:40:00.000 55400CIV3D_2008_0F 38 0
AUHBRPS01 2011-06-20 09:40:00.000 55600BLDSYS_2008_0F 116 0
AUHBRPS01 2011-06-20 09:40:00.000 55700REVSYP_2008_0F 10 0
AUHBRPS01 2011-06-20 09:40:00.000 59700CIV3D_2009_0F 38 0
AUHBRPS01 2011-06-20 09:40:00.000 59900BLDSYS_2009_0F 116 0
AUHBRPS01 2011-06-20 09:40:00.000 60000REVSYP_2009_0F 10 0
AUHBRPS01 2011-06-20 09:40:00.000 64900CIV3D_F 38 34
AUHBRPS01 2011-06-20 09:40:00.000 65100BLDSYS_F 116 98
AUHBRPS01 2011-06-20 09:40:00.000 65200REVSYP_F 10 0
AUHBRPS01 2011-06-20 09:40:00.000 73000BLDSYS_2010_0F 116 0
AUHBRPS01 2011-06-20 09:40:00.000 73100REVSYP_2010_0F 10 0
AUHBRPS01 2011-06-20 09:40:00.000 73700CIV3D_2010_0F 38 0
AUHBRPS01 2011-06-20 09:40:00.000 85562BLDSYS_2011_0F 116 98
AUHBRPS01 2011-06-20 09:40:00.000 85604CIV3D_2011_0F 38 34
AUHBRPS01 2011-06-20 09:40:00.000 85624REVSYP_2011_0F 10 0
AUHBRPS01 2011-06-20 09:40:00.000 85734BLDSYS_2012_0F 116 0
AUHBRPS01 2011-06-20 09:40:00.000 85793REVSYP_2012_0F 10 0
AUHBRPS01 2011-06-20 09:40:00.000 85830CIV3D_2012_0F 38 0
AUHBRPS01 2011-06-20 09:40:00.000 PLIST 2 0
AUHBRPS01 2011-06-20 09:50:00.000 55400CIV3D_2008_0F 38 0
AUHBRPS01 2011-06-20 09:50:00.000 55600BLDSYS_2008_0F 116 0
AUHBRPS01 2011-06-20 09:50:00.000 55700REVSYP_2008_0F 10 0
AUHBRPS01 2011-06-20 09:50:00.000 59700CIV3D_2009_0F 38 0
AUHBRPS01 2011-06-20 09:50:00.000 59900BLDSYS_2009_0F 116 0
AUHBRPS01 2011-06-20 09:50:00.000 60000REVSYP_2009_0F 10 0
AUHBRPS01 2011-06-20 09:50:00.000 64900CIV3D_F 38 34
AUHBRPS01 2011-06-20 09:50:00.000 65100BLDSYS_F 116 96
AUHBRPS01 2011-06-20 09:50:00.000 65200REVSYP_F 10 0
AUHBRPS01 2011-06-20 09:50:00.000 73000BLDSYS_2010_0F 116 0
AUHBRPS01 2011-06-20 09:50:00.000 73100REVSYP_2010_0F 10 0
AUHBRPS01 2011-06-20 09:50:00.000 73700CIV3D_2010_0F 38 0
AUHBRPS01 2011-06-20 09:50:00.000 85562BLDSYS_2011_0F 116 96
AUHBRPS01 2011-06-20 09:50:00.000 85604CIV3D_2011_0F 38 34
AUHBRPS01 2011-06-20 09:50:00.000 85624REVSYP_2011_0F 10 0
AUHBRPS01 2011-06-20 09:50:00.000 85734BLDSYS_2012_0F 116 0
AUHBRPS01 2011-06-20 09:50:00.000 85793REVSYP_2012_0F 10 0
AUHBRPS01 2011-06-20 09:50:00.000 85830CIV3D_2012_0F 38 0
AUHBRPS01 2011-06-20 09:50:00.000 PLIST 2 0
AUHBRPS01 2011-06-20 10:00:00.000 55400CIV3D_2008_0F 38 0
AUHBRPS01 2011-06-20 10:00:00.000 55600BLDSYS_2008_0F 116 0
AUHBRPS01 2011-06-20 10:00:00.000 55700REVSYP_2008_0F 10 0
AUHBRPS01 2011-06-20 10:00:00.000 59700CIV3D_2009_0F 38 0
AUHBRPS01 2011-06-20 10:00:00.000 59900BLDSYS_2009_0F 116 0
AUHBRPS01 2011-06-20 10:00:00.000 60000REVSYP_2009_0F 10 0
AUHBRPS01 2011-06-20 10:00:00.000 64900CIV3D_F 38 34

i only showed DateTime 2011-06-20 there are thousands of records with other dates as well

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-30 : 20:09:40

edited my last post

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources