Difficult Query question: TOP 1 + UNION + GROUP BY?!

Hi,
I need to perform an SQL Query, but I'm not able to find out how. It should
do the following stuff:
I have a table (tblSMS) that contains different SMS-records (each record
with a Number, a SendDate (DateTime-field) and a Campaign).
When I receive an SMS I need to find the following record(s):
- All the records with a SendDate between now (GETDATE() ) and the last 48
hours AND the Number-Field should be equal to my input
- But: In all the records that it selects, there can't be 2 with the same
Campaign
- At least the last record which has the same Number (ORDER BY SendDate = DateTime field) should be selected, even when the SendDate is smaller than
GETDATE - 48 hours...
This query works for the first and third objectives, but not for the
second...
SELECT * FROM
(SELECT TOP 1 tblSMS.* FROM tblSMS
WHERE (Number = '1234567890')
ORDER BY SendDate DESC) X
UNION
SELECT DISTINCT tblSMS.* FROM tblSMS
WHERE ((Number = '1234567890'))
AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
ORDER BY SendDate DESC
Does anybody knows how to do this?
Thanks a lot in advance,
PieterFirst, I don't understand what you mean exactly by your second statement "In
all the records that it selects, there can't be 2 with the same Campaign".
A little more explanation, with an example of a false result, should be
given.
Second, there is nothing in the WHERE clauses of your query about the
Campaign field.
Third, I'm not sure about the usefullness of the "SELECT * FROM (subquery
here) X" statement.
S. L.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to perform an SQL Query, but I'm not able to find out how. It
> should
> do the following stuff:
> I have a table (tblSMS) that contains different SMS-records (each record
> with a Number, a SendDate (DateTime-field) and a Campaign).
> When I receive an SMS I need to find the following record(s):
> - All the records with a SendDate between now (GETDATE() ) and the last 48
> hours AND the Number-Field should be equal to my input
> - But: In all the records that it selects, there can't be 2 with the same
> Campaign
> - At least the last record which has the same Number (ORDER BY SendDate => DateTime field) should be selected, even when the SendDate is smaller than
> GETDATE - 48 hours...
> This query works for the first and third objectives, but not for the
> second...
> SELECT * FROM
> (SELECT TOP 1 tblSMS.* FROM tblSMS
> WHERE (Number = '1234567890')
> ORDER BY SendDate DESC) X
> UNION
> SELECT DISTINCT tblSMS.* FROM tblSMS
> WHERE ((Number = '1234567890'))
> AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> ORDER BY SendDate DESC
> Does anybody knows how to do this?
> Thanks a lot in advance,
> Pieter
>|||Well, my table tblSMS contains the fields "Campaign", "Number", "SendDate",
...
So when I don't want two select two records with the same Campaign I want to
say with that that there can't be 2 or more records selected thath has in
the field "Campaign" the same value.
Indeed there is nothing in the WHERE clause about the campaign-field, that's
the reason I wrote that the query works for the first and third objective,
but not for the second (the one with the Campaign-stuff).
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OAA8v3r5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> First, I don't understand what you mean exactly by your second statement
"In
> all the records that it selects, there can't be 2 with the same Campaign".
> A little more explanation, with an example of a false result, should be
> given.
> Second, there is nothing in the WHERE clauses of your query about the
> Campaign field.
> Third, I'm not sure about the usefullness of the "SELECT * FROM (subquery
> here) X" statement.
> S. L.
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I need to perform an SQL Query, but I'm not able to find out how. It
> > should
> > do the following stuff:
> > I have a table (tblSMS) that contains different SMS-records (each record
> > with a Number, a SendDate (DateTime-field) and a Campaign).
> >
> > When I receive an SMS I need to find the following record(s):
> > - All the records with a SendDate between now (GETDATE() ) and the last
48
> > hours AND the Number-Field should be equal to my input
> > - But: In all the records that it selects, there can't be 2 with the
same
> > Campaign
> > - At least the last record which has the same Number (ORDER BY SendDate
=> > DateTime field) should be selected, even when the SendDate is smaller
than
> > GETDATE - 48 hours...
> >
> > This query works for the first and third objectives, but not for the
> > second...
> > SELECT * FROM
> > (SELECT TOP 1 tblSMS.* FROM tblSMS
> > WHERE (Number = '1234567890')
> > ORDER BY SendDate DESC) X
> > UNION
> > SELECT DISTINCT tblSMS.* FROM tblSMS
> > WHERE ((Number = '1234567890'))
> > AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> > ORDER BY SendDate DESC
> >
> > Does anybody knows how to do this?
> >
> > Thanks a lot in advance,
> >
> > Pieter
> >
> >
>|||Unfortunately, you cannot define a query in terms of what it can NOT select.
You specify criteria that the rows must match. In this case, you need to
define what logic is used to select only 1 of many rows that match a
campaign. Since the last part of the union query you posted uses distinct,
I'm guessing that there is no real PK for the table; perhaps there is
nothing that can be relied on to uniquely identify a particular row. If so,
you may need to use a temp table along with some sort of identity column to
generate sufficient unique infomation for each row to accomplish your goal.
Once you have a way of uniquely identifying a row, you can use min, max, or
top to limit rows by campaign. Or use a cursor.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23qUydd25EHA.824@.TK2MSFTNGP11.phx.gbl...
> Well, my table tblSMS contains the fields "Campaign", "Number",
"SendDate",
> ...
> So when I don't want two select two records with the same Campaign I want
to
> say with that that there can't be 2 or more records selected thath has in
> the field "Campaign" the same value.
> Indeed there is nothing in the WHERE clause about the campaign-field,
that's
> the reason I wrote that the query works for the first and third objective,
> but not for the second (the one with the Campaign-stuff).
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:OAA8v3r5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > First, I don't understand what you mean exactly by your second statement
> "In
> > all the records that it selects, there can't be 2 with the same
Campaign".
> > A little more explanation, with an example of a false result, should be
> > given.
> >
> > Second, there is nothing in the WHERE clauses of your query about the
> > Campaign field.
> >
> > Third, I'm not sure about the usefullness of the "SELECT * FROM
(subquery
> > here) X" statement.
> >
> > S. L.
> >
> > "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
> > > Hi,
> > >
> > > I need to perform an SQL Query, but I'm not able to find out how. It
> > > should
> > > do the following stuff:
> > > I have a table (tblSMS) that contains different SMS-records (each
record
> > > with a Number, a SendDate (DateTime-field) and a Campaign).
> > >
> > > When I receive an SMS I need to find the following record(s):
> > > - All the records with a SendDate between now (GETDATE() ) and the
last
> 48
> > > hours AND the Number-Field should be equal to my input
> > > - But: In all the records that it selects, there can't be 2 with the
> same
> > > Campaign
> > > - At least the last record which has the same Number (ORDER BY
SendDate
> => > > DateTime field) should be selected, even when the SendDate is smaller
> than
> > > GETDATE - 48 hours...
> > >
> > > This query works for the first and third objectives, but not for the
> > > second...
> > > SELECT * FROM
> > > (SELECT TOP 1 tblSMS.* FROM tblSMS
> > > WHERE (Number = '1234567890')
> > > ORDER BY SendDate DESC) X
> > > UNION
> > > SELECT DISTINCT tblSMS.* FROM tblSMS
> > > WHERE ((Number = '1234567890'))
> > > AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> > > ORDER BY SendDate DESC
> > >
> > > Does anybody knows how to do this?
> > >
> > > Thanks a lot in advance,
> > >
> > > Pieter
> > >
> > >
> >
> >
>|||Pieter,
I think this may work for you. The WITH TIES is a trick that
uses the CASE expression in the ORDER BY clause to select
all records within the last two days as tied, but only a single
older record if there are no recent ones. Give it a try, even
if it looks wrong at first!
So this identifies the most recent 1234567890 row for
each Campaign value, regardless of date, then returns to you
only the top 1 with ties, which is either all results within the
last 2 days, or the one most recent result from earlier.
SELECT TOP 1 WITH TIES *
FROM tblSMS as t1
WHERE Number = '1234567890'
AND NOT EXISTS (
SELECT * FROM tblSMS as t2
WHERE t2.Number = '1234567890'
AND t2.Campaign = t1.Campaign
AND t2.SendDate > t1.SendDate
)
ORDER BY
CASE WHEN SendDate <= getdate() - 2
THEN SendDate
ELSE 0 END
GO
Steve Kass
Drew University
DraguVaso wrote:
>Hi,
>I need to perform an SQL Query, but I'm not able to find out how. It should
>do the following stuff:
>I have a table (tblSMS) that contains different SMS-records (each record
>with a Number, a SendDate (DateTime-field) and a Campaign).
>When I receive an SMS I need to find the following record(s):
>- All the records with a SendDate between now (GETDATE() ) and the last 48
>hours AND the Number-Field should be equal to my input
>- But: In all the records that it selects, there can't be 2 with the same
>Campaign
>- At least the last record which has the same Number (ORDER BY SendDate =>DateTime field) should be selected, even when the SendDate is smaller than
>GETDATE - 48 hours...
>This query works for the first and third objectives, but not for the
>second...
>SELECT * FROM
> (SELECT TOP 1 tblSMS.* FROM tblSMS
> WHERE (Number = '1234567890')
> ORDER BY SendDate DESC) X
>UNION
> SELECT DISTINCT tblSMS.* FROM tblSMS
> WHERE ((Number = '1234567890'))
> AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> ORDER BY SendDate DESC
>Does anybody knows how to do this?
>Thanks a lot in advance,
>Pieter
>
>