January 7, 2008 at 11:41 am
Hello guys,
I need to write a query to filter some result from the above:
2008-01-07 00:09:35326A7D9AC1592115E4F2E944B2AA8D19
2008-01-07 00:09:35786124F1FCC4A20D04CA09842D2F7658
2008-01-07 00:21:26326A7D9AC1592115E4F2E944B2AA8D19
2008-01-07 00:21:26660DF68DE09440FD689DB7FB7FAE55A3
....
Basic idea is I want only the first two records: 2008-01-07 00:09
How do I do this?
Thanks lots.
January 7, 2008 at 11:44 am
Check out the TOP clause in Books Online. That might help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2008 at 11:51 am
The question is the result might contain more than 2 records for the same timestamp, using top is probably not right.
January 7, 2008 at 12:03 pm
Are those two seperate fields? If not, ORDER BY with a TOP will get what you want. If what you're trying to do is find all the records for a minimum date you could use a group by in a sub-select.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2008 at 12:20 pm
Do you want the two records with the lowest dates, or all the records that have the minimum date in the table?
If the first, try top. If the second...
SELECT TheDate, TheGuid FROM TheTable WHERE TheDate =
(SELECT MIN(TheDate) FROM TheTable)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2008 at 1:06 pm
Or using a sub query you can do this. The following will return all rows that have the top most value.
SELECT t.TheDate, t.TheGuid
FROM TheTable t
INNER JOIN (
SELECT
MIN(TheDate) as TheDate
FROM TheTable
) tmin on (tmin.TheDate = t.TheDate)
This also enables us to do the same thing for a sub selection of the table too:
SELECT t.someCategory, t.TheDate, t.TheGuid
FROM TheTable t
INNER JOIN (
SELECT
someCategory,
MIN(TheDate) as TheDate
FROM TheTable
GROUP BY
someCategory
) tmin on (tmin.someCategory = t.someCategoy and tmin.TheDate = t.TheDate)
WHERE t.someCategory = 'selected value'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply