| Author |
 Topic  |
|
|
shajimanjeri
Posting Yak Master
India
179 Posts |
 Posted - 05/14/2008 : 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
Sweden
29156 Posts |
Posted - 05/14/2008 : 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
India
48115 Posts |
Posted - 05/14/2008 : 09:28:29
|
SELECT *
FROM Reports
WHERE EventId=(SELECT MAX(EventId)
FROM Reports)
This will at any time provide you with recently inserted 100,000 records. |
|
|
|
shajimanjeri
Posting Yak Master
India
179 Posts |
Posted - 05/14/2008 : 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
Sweden
29156 Posts |
Posted - 05/14/2008 : 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
India
48115 Posts |
Posted - 05/14/2008 : 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
India
179 Posts |
Posted - 05/14/2008 : 10:09:16
|
Thanks Peso,
Its working fine!!
:) |
|
|
| |
Topic |
|
Distince query
