what is the problem with this query

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

what is the problem with this query

Author

Topic

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-07-02 : 06:20:25

this is my table (extract)



Project         PoweUptime              dateAdded
Astro DMR	57.789999999999999	2009-01-01 00:00:00.000
Astro DMR	5.0	                2009-06-23 11:41:01.000

if i run this query


select project, poweuptime,dateadded from summary project where project='astro dmr'

i am getting



Project         PoweUptime                   dateAdded 
Astro DMR	57.789999999999999	2009-01-01 00:00:00.000
Astro DMR	5.0	                2009-06-23 11:41:01.000

if i rum this query


select project, PoweUpTime, dateadded from summary where  project='astro dmr' and dateadded in ( select max(dateadded) from summary group by project)  order by project

i am still getting same thing



Project         PoweUptime                   dateAdded
Astro DMR	57.789999999999999	2009-01-01 00:00:00.000
Astro DMR	5.0	                2009-06-23 11:41:01.000

actually i am expecting:



Project         PoweUptime                   dateAdded
Astro DMR	5.0	                2009-06-23 11:41:01.000

what i need is for each project i need ONE row which is added RECENTLY
what is the problem with the second query

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 06:26:05

[code]
select project,
PoweUpTime,
dateadded
from summary
where project = 'astro dmr'
and dateadded in ( select max(dateadded) from summary ~~group by project~~)
order by project
[/code]

you used group by project in the sub query. So you are getting the max dateadded by project

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 06:28:05

this will be better. The max(dateadded) will be depending on the filtered project value in your where clause.


select project, 
       PoweUpTime, 
       dateadded 
from   summary s
where  project = 'astro dmr' 
and    dateadded in ( select max(dateadded) from summary x where x.proejct = s.project)
order by project

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

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-07-02 : 06:36:14

In fact there are other values in my table

this is my table

Project	PoweUpTime	DateAdded
----------	------------------	----------------
a	128	1/1/2009
Astro DMR	5	6/23/2009
Astro DMR	57.79	1/1/2009
b	56.96	1/1/2009
SDFG	82.34333333	6/10/2009
GSDF	28.43	1/1/2009
G	0	1/1/2009
SDF	0	1/1/2009
G	43.83	1/1/2009
SDF	54.17	1/1/2009
G	59	1/1/2009
SDF	0	5/22/2009
G	21.05	1/1/2009
SDF	33.58	1/1/2009
G	35.17	1/1/2009
SDFGSDFG	0	1/1/2009
FFF	23.33	1/1/2009
HHH	24.6	1/1/2009
JJERT	141	1/1/2009
F	141	1/1/2009
E	30	1/1/2009
RTYERT	0	5/25/2009
ERTYERT	0	5/29/2009
ERTYERT	82	6/8/2009
ET	105.66	1/1/2009
ERTYERT	72.4	1/1/2009
ERTYERT	64.06	1/1/2009
ETRY	58.4	1/1/2009
IIII	96.6	1/1/2009
FASD	65.64	1/1/2009
GDFG	37.9	1/1/2009
DFG	23.8	1/1/2009
SDF	6	6/10/2009
G	83.33	1/1/2009
DFG	0	1/1/2009
DFG	0	1/1/2009
FG	293.4	1/1/2009

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 06:37:53

so just use the last query i posted.

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

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-07-02 : 06:45:59

in both the cases it will return ONLY 1 row

Astro DMR 5.0 2009-06-23 11:41:01.000

but for each project i want ONE such row

my query was right,


select project, PoweUpTime, dateadded from summary where  project='astro dmr' and dateadded in ( select max(dateadded) from summary group by project)  order by project

since there are too many cuplicate dates -1/1/2009 - it will return almost all rows

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 06:58:51

[code]SELECT Project,
PowerUpTime,
DateAdded
FROM (
SELECT Project,
PowerUpTime,
DateAdded,
ROW_NUMBER() OVER (PARTITION BY Project ORDER BY DateAdded DESC) AS recID
) AS d
WHERE recID = 1[/code]

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-02 : 07:32:27

use the query i posted on 07/02/2009 : 06:26:05

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-02 : 11:56:34

another way

select project, 
       PoweUpTime, 
       dateadded 
from   
	summary s
INNER JOIN
( 
	select Project, MAX(dateadded) AS DateAdded 
	from summary
	GROUP BY Project
) AS T
	S.proejct = T.project
	AND S.DateAdded = T.DateAdded
where  
	project = 'astro dmr' 
order by 
	project

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-02 : 12:03:41

quote:
Originally posted by krishna_yess

my query was right,

select project, PoweUpTime, dateadded from summary where project='astro dmr' and dateadded in ( select max(dateadded) from summary group by project) order by project

since there are too many cuplicate dates -1/1/2009 - it will return almost all rows

I'm not sure about right.. Syntactically correct, yes.

The reason it is not getting the results you want is because your IN clause is getting ALL the max dates by project. So, as you discovered, a lot of projects have that same date.

In order to make sure you get the correct row you need to look at the MAX date by project, not all projects. So, you can use the suggestion made by Peso or myself. Khtan's query will also work, but only for a single Project. Which is what you initially indicated you wanted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources