selecting a single in thesame date

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 2000 Forums

SQL Server Development (2000)

selecting a single in thesame date

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-08-31 : 06:16:31

i have a table liek this
id(autoincement),companyid,datetime
i want to make a query and get for each day all the companyid once in a day+there date and there id
so if there is companyid in a certain day i will get it only once (dosent matter which one of them)
how can i do it
thnaks i nadvance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 06:29:06

[code]SELECT DATEADD(day, DATEDIFF(day, 0, [DateTime]), 0),
CompanyID,
MAX(ID)
FROM MyTable
GROUP BY DATEADD(day, DATEDIFF(day, 0, [DateTime]), 0),
CompanyID
ORDER BY DATEADD(day, DATEDIFF(day, 0, [DateTime]), 0),
CompanyID[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 06:34:19

Or this one

SELECT		mt.*
FROM		MyTable mt
INNER JOIN	(
			SELECT		MAX(ID) mid
			FROM		MyTable
			GROUP BY	DATEADD(day, DATEDIFF(day, 0, [DateTime]), 0),
					CompanyID
		) d ON d.mid = mt.ID
ORDER BY	mt.[DateTime],
		mt.CompanyID


SELECT		mt.*
FROM		MyTable mt
WHERE		mt.ID IN (SELECT MAX(t.ID) FROM MyTable t GROUP BY DATEADD(day, DATEDIFF(day, 0, t.[DateTime]), 0), t.CompanyID) = mt.ID
ORDER BY	mt.[DateTime],
		mt.CompanyID

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-08-31 : 10:29:01

thnaks alot

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources