group by ? max date ? - 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 2008 Forums

Transact-SQL (2008)

group by ? max date ?

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-30 : 08:33:03

[code]
1 49 21/06/2010 0:00:00 NULL 1 1
3 67 21/06/2010 0:00:00 NULL 1 3
4 69 21/06/2010 0:00:00 NULL 1 3
5 49 23/06/2010 0:00:00 NULL 1 3
[/code]

so the first field is id, then userid, startdate,stopdate ,locationid, and mooo not relevant

i just want this..

[code]
5 49 23/06/2010 0:00:00 NULL 1 3
3 67 21/06/2010 0:00:00 NULL 1 3
4 69 21/06/2010 0:00:00 NULL 1 3

[/code]

i want all the results with the startdate being the highest

thx alot in advance, and yes i seem to be too stupid to figure this one out :(

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-30 : 08:42:17

select * from
(
select row_number() over (partition by userid order by startdate desc) as sno,* from table
) as t
where sno=1

Madhivanan

Failing to plan is Planning to fail

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-30 : 08:46:05

[code]
SELECT TOP (100) PERCENT userid, MAX(begindatum) AS begindatum, einddatum, locatieid
FROM dbo.tblusers_planning
WHERE (begindatum IS NOT NULL)
GROUP BY userid, einddatum, locatieid
ORDER BY begindatum DESC

[/code]

this works but if i add the enddate column, it wil not work anymore

edit: damn, so many posts, ur reply is a bit confusing to me , sorry :(

edit2: i got it !

thx to you mate ! to say thx ill order indian food this evening :p

select * from
(
select row_number() over (partition by userid order by begindatum desc) as sno,* from tblusers_planning
) as t
where sno=1

didnt know about that OVER statement, ill google it

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-30 : 08:57:08

weird my sql server 2005 says the OVER statement isnt supported, but the query does the job
and i see the OVER statement in my query

what do i do ? repost this onto the sqlserver 2005 forum or just keep it
every time i save it , it says that the OVER statement is not supported, but it works

on my job i got 2005 at home i got 2008.... thats actually pretty bad, now i think about it :p

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-30 : 09:13:41

And again, please set your compatibility level to 90 or higher for using the ROW_NUMBER function.

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

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-30 : 09:31:48

okay i do this with the following line ?

EXEC sp_dbcmptlevel databasename, 90;

How do i get the current ( default ) value ? in case i want to switch it back.

and what does it mean, is this db then compatible with all 2008 sql server statements?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 02:12:08

To know the current compatibility level, run

EXEC sp_helpdb your_db

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 02:12:52

<<
thx to you mate ! to say thx ill order indian food this evening :p
>>

Thanks. I hope you had it

Madhivanan

Failing to plan is Planning to fail

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-01 : 07:28:30

i did, how was it called again, yellow rice with someting bbq like :p
talked to the owner while i was waiting like an half hour, nice guy :p

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 07:38:09

quote:
Originally posted by doubleotwo

i did, how was it called again, yellow rice with someting bbq like :p
talked to the owner while i was waiting like an half hour, nice guy :p

I think it is called Biryani.
http://en.wikipedia.org/wiki/Biryani

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 08:03:42

quote:
Originally posted by Idera

quote:
Originally posted by doubleotwo

i did, how was it called again, yellow rice with someting bbq like :p
talked to the owner while i was waiting like an half hour, nice guy :p

I think it is called Biryani.
http://en.wikipedia.org/wiki/Biryani

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

You must have tasted it a lot

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 08:10:50

Yup.
Cant resist the aroma of it and ofcourse the taste.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources