Distince query - 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

General SQL Server Forums

New to SQL Server Programming

Distince query

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-05-14 : 08:36:12

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

30421 Posts

Posted - 2008-05-14 : 08:44:16

EventID is unique, and you want the distinct EventID?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 09:28:29

[code]SELECT *
FROM Reports
WHERE EventId=(SELECT MAX(EventId)
FROM Reports)
[/code]

This will at any time provide you with recently inserted 100,000 records.

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-05-14 : 09:30:22

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 09:35:11

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"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 09:42:59

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.

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-05-14 : 10:09:16

Thanks Peso,
Its working fine!!
:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources