SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Distince query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shajimanjeri
Posting Yak Master

India
179 Posts

Posted - 05/14/2008 :  08:36:12  Show Profile  Reply with Quote
I have a table named Reports with below fields
Id - numeric
EventId - numeric
SmsBody - varchar(2000)
SmsTime - datetime
Tagname - varchar (15).
Today this table stores 100,000 records with unique EventId. In the same way this tables stores 100,000 records every day with a unique EventId. EventId is will change by days only.
Now here I need to see the records only one time for each EventId. I mean showing recrods by distinct eventId. How to write the query here?

regards,
Shaji

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 05/14/2008 :  08:44:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
EventID is unique, and you want the distinct EventID?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/14/2008 :  09:28:29  Show Profile  Reply with Quote
SELECT * 
FROM Reports 
WHERE EventId=(SELECT MAX(EventId) 
               FROM Reports)


This will at any time provide you with recently inserted 100,000 records.
Go to Top of Page

shajimanjeri
Posting Yak Master

India
179 Posts

Posted - 05/14/2008 :  09:30:22  Show Profile  Reply with Quote
let me explain an example: I have 100,000 records with EventId 105. Instead of showing all these records for this EventId (105) just show only one record for this EventId. Same way I have another 100,000 records with EventId 106.Instead of showing all these records for this EventId (106) just show only one record for this EventId. How to write this query?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 05/14/2008 :  09:35:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
But then EventID is not UNIQUE, as you wrote initially.

Here is a solution for you.

SELECT r.Id, r.EventId, r.SmsBody, r.SmsTime, r.Tagname
FROM (SELECT MIN(ID) AS ID FROM Reports GROUP BY EventID) AS d
INNER JOIN Reports AS r ON r.ID = d.ID



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/14/2008 :  09:42:59  Show Profile  Reply with Quote
quote:
Originally posted by shajimanjeri

let me explain an example: I have 100,000 records with EventId 105. Instead of showing all these records for this EventId (105) just show only one record for this EventId. Same way I have another 100,000 records with EventId 106.Instead of showing all these records for this EventId (106) just show only one record for this EventId. How to write this query?


Ok. Then do like this

SELECT EventID,MAX(SmsBody),MAX(SmsTime),MAX(Tagname)
FROM Reports
GROUP BY EventID

This will give you 1 record per EVentID but this will just give you some random values for all your other fields for a particular value of EventID. You havent told about any business rules of what you should get for other fields. If you can specify it, we can provide you with an accurate solution.
Go to Top of Page

shajimanjeri
Posting Yak Master

India
179 Posts

Posted - 05/14/2008 :  10:09:16  Show Profile  Reply with Quote
Thanks Peso,
Its working fine!!
:)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000