DISTINCT QUERY

This is probably easy but I can't work it out. I have this statement

SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.Name
FROM dbo.CIF_Departures INNER JOIN
dbo.CIF_PlaceReference ON dbo.CIF_Departures.EndPoint
= dbo.CIF_PlaceReference.PlaceID
ORDER BY dbo.CIF_PlaceReference.Name

This results in a column of placenames which is OK. There are also multiple
'time of day' values against each placename however I only want to return
the one nearest to the current time. If I do this...

SELECT DISTINCT TOP 100 PERCENT
dbo.CIF_PlaceReference.Name,dbo.CIF_Departures.Sta rtTime
FROM dbo.CIF_Departures INNER JOIN
dbo.CIF_PlaceReference ON dbo.CIF_Departures.EndPoint
= dbo.CIF_PlaceReference.PlaceID
ORDER BY dbo.CIF_PlaceReference.Name

.... I get multiple place names.

Any ideas?

re: DISTINCT QUERY

"Richard" <richard.spare@ntlworld.com (nospam>) writes:[color=blue]
> This is probably easy but I can't work it out. I have this statement
>
> SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.Name
> FROM dbo.CIF_Departures INNER JOIN
> dbo.CIF_PlaceReference ON
> dbo.CIF_Departures.EndPoint >= dbo.CIF_PlaceReference.PlaceID
> ORDER BY dbo.CIF_PlaceReference.Name
>
> This results in a column of placenames which is OK. There are also
> multiple 'time of day' values against each placename however I only want
> to return the one nearest to the current time. If I do this...
>
> SELECT DISTINCT TOP 100 PERCENT
> dbo.CIF_PlaceReference.Name,dbo.CIF_Departures.Sta rtTime
> FROM dbo.CIF_Departures INNER JOIN
> dbo.CIF_PlaceReference ON
> dbo.CIF_Departures.EndPoint>= dbo.CIF_PlaceReference.PlaceID
> ORDER BY dbo.CIF_PlaceReference.Name
>
> ... I get multiple place names.[/color]

Of course. If you would get disctinct names, how do you think SQL Server
would be able to find out which StartTimes you want? DISTINCT applies
to all columns.

Your requirement is not wholly clear, so I present a simple solution,
you simply get the latest starttime:

SELECT pr.Name, MAX(d.StartTime)
FROM dbo.CIF_Departures d
JOIN dbo.CIF_PlaceReference pr ON d.EndPoint = pr.PlaceID
GROUP BY pr.Name
ORDER BY pr.Name

If this does meet your requirement, please post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o Desired output from this sample.

This reduces the amount of guessing that anyone that helps you has
to do, and it also makes it simple to provide a tested solution.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: DISTINCT QUERY

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns94A426F0918EYazorman@127.0.0.1...[color=blue]
> "Richard" <richard.spare@ntlworld.com (nospam>) writes:[color=green]
> > This is probably easy but I can't work it out. I have this statement
> >
> > SELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.Name
> > FROM dbo.CIF_Departures INNER JOIN
> > dbo.CIF_PlaceReference ON
> > dbo.CIF_Departures.EndPoint >= dbo.CIF_PlaceReference.PlaceID
> > ORDER BY dbo.CIF_PlaceReference.Name
> >
> > This results in a column of placenames which is OK. There are also
> > multiple 'time of day' values against each placename however I only want
> > to return the one nearest to the current time. If I do this...
> >
> > SELECT DISTINCT TOP 100 PERCENT
> > dbo.CIF_PlaceReference.Name,dbo.CIF_Departures.Sta rtTime
> > FROM dbo.CIF_Departures INNER JOIN
> > dbo.CIF_PlaceReference ON
> > dbo.CIF_Departures.EndPoint>= dbo.CIF_PlaceReference.PlaceID
> > ORDER BY dbo.CIF_PlaceReference.Name
> >
> > ... I get multiple place names.[/color]
>
> Of course. If you would get disctinct names, how do you think SQL Server
> would be able to find out which StartTimes you want? DISTINCT applies
> to all columns.
>
> Your requirement is not wholly clear, so I present a simple solution,
> you simply get the latest starttime:
>
> SELECT pr.Name, MAX(d.StartTime)
> FROM dbo.CIF_Departures d
> JOIN dbo.CIF_PlaceReference pr ON d.EndPoint = pr.PlaceID
> GROUP BY pr.Name
> ORDER BY pr.Name
>
> If this does meet your requirement, please post:
>
> o CREATE TABLE statements for your tables.
> o INSERT statements with sample data.
> o Desired output from this sample.
>
> This reduces the amount of guessing that anyone that helps you has
> to do, and it also makes it simple to provide a tested solution.
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

Thanks Erland

This goes some way to helping except instead of the MAX(d.starttime), i need
the the nearest record to the current time.

CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
'22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
'22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
'10:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
'10:00:00')
CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1', 'Here')
INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2', 'There')

If the time now is 21:59. I need a query that returns:
Here 22:00:00
There 22:00:00

If the time now is 22:01

Here Null
There Null

Regards
Richard

re: DISTINCT QUERY

"Richard" <richard.spare@ntlworld.com (nospam>) writes:[color=blue]
> This goes some way to helping except instead of the MAX(d.starttime), i
> need the the nearest record to the current time.
>
> CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> '22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> '22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
> '10:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
> '10:00:00')
> CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
> INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1', 'Here')
> INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2', 'There')
>
> If the time now is 21:59. I need a query that returns:
> Here 22:00:00
> There 22:00:00
>
> If the time now is 22:01
>
> Here Null
> There Null[/color]

Your definition of "nearest record" still eludes me. From the narrative,
it is not obvious why 22:00:00 should not be returned when current time
is 22:01. But the sample output makes it clear what you want.

Here is a query that almost gives the desired output. Almost, because
it is impossible to return 22:00:00 for There, as this time is not given
for There.

declare @now datetime
select @now = '20040306 21:59'

SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
FROM dbo.tmpPlaceReference pr
LEFT JOIN dbo.tmpDepartures d
ON d.endpoint = pr.PlaceID
AND d.starttime > convert(char(8), @now, 108)
GROUP BY pr.Name
ORDER BY pr.Name

The convert stuff is need because there is no time data type in SQL
Server. SQL Server accepts '10:00:00' for input to a datetime value,
but that actually means '19000101 10:00:00'. Convert takes a couple
of format codes for datetime values, 108 is for time only.

Note that if @now is 23:59 and there is a departure at midnight, that
depature will not be listed.

Finally a note about your script: it's a good idea to run it and check
before you post. I can tell that you hadn't, because the datetime(8)
gave me a syntax error.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: DISTINCT QUERY

Earliest StartTime greater than or equal to now for each Placeid:

SELECT P.name,
(SELECT MIN(starttime)
FROM tmpDepartures
WHERE endpoint = P.placeid
AND starttime >= CONVERT(VARCHAR,CURRENT_TIMESTAMP,14))
FROM tmpPlaceReference AS P

This assumes you are only interested in times not dates. DATETIME stores
both but apparently you are using the "default" date value of 1900-01-01. If
the date is in fact significant then just replace the CONVERT expression
with CURRENT_TIMESTAMP.

--
David Portas
SQL Server MVP
--

re: DISTINCT QUERY

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns94A51662B5A8Yazorman@127.0.0.1...[color=blue]
> "Richard" <richard.spare@ntlworld.com (nospam>) writes:[color=green]
> > This goes some way to helping except instead of the MAX(d.starttime), i
> > need the the nearest record to the current time.
> >
> > CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > '22:00:00')
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > '22:00:00')
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
> > '10:00:00')
> > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
> > '10:00:00')
> > CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
> > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1', 'Here')
> > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2', 'There')
> >
> > If the time now is 21:59. I need a query that returns:
> > Here 22:00:00
> > There 22:00:00
> >
> > If the time now is 22:01
> >
> > Here Null
> > There Null[/color]
>
> Your definition of "nearest record" still eludes me. From the narrative,
> it is not obvious why 22:00:00 should not be returned when current time
> is 22:01. But the sample output makes it clear what you want.
>
> Here is a query that almost gives the desired output. Almost, because
> it is impossible to return 22:00:00 for There, as this time is not given
> for There.
>
> declare @now datetime
> select @now = '20040306 21:59'
>
> SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
> FROM dbo.tmpPlaceReference pr
> LEFT JOIN dbo.tmpDepartures d
> ON d.endpoint = pr.PlaceID
> AND d.starttime > convert(char(8), @now, 108)
> GROUP BY pr.Name
> ORDER BY pr.Name
>
> The convert stuff is need because there is no time data type in SQL
> Server. SQL Server accepts '10:00:00' for input to a datetime value,
> but that actually means '19000101 10:00:00'. Convert takes a couple
> of format codes for datetime values, 108 is for time only.
>
> Note that if @now is 23:59 and there is a departure at midnight, that
> depature will not be listed.
>
> Finally a note about your script: it's a good idea to run it and check
> before you post. I can tell that you hadn't, because the datetime(8)
> gave me a syntax error.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

Despite my errors with the datetime in CREATETABLE and the fact I got the
INSERT wrong also..should have been:

INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
'22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES
('2','22:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES
('1,'10:00:00')
INSERT INTO tmpDepartures (endpoint , starttime) VALUES
('2','10:00:00')

.... you did well to give me the answer (sorry about that).

All seems to work well .. Many thanks for your help.

Regards
Richard

re: DISTINCT QUERY

"Richard >" <richard.spare@ntlworld.com<nospam> wrote in message
news:Sct2c.24058$gC2.23350@newsfe5-gui.server.ntli.net...[color=blue]
>
> "Erland Sommarskog" <sommar@algonet.se> wrote in message
> news:Xns94A51662B5A8Yazorman@127.0.0.1...[color=green]
> > "Richard" <richard.spare@ntlworld.com (nospam>) writes:[color=darkred]
> > > This goes some way to helping except instead of the MAX(d.starttime),[/color][/color][/color]
i[color=blue][color=green][color=darkred]
> > > need the the nearest record to the current time.
> > >
> > > CREATE TABLE tmpDepartures(endpoint char(12), starttime datetime(8))
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > > '22:00:00')
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> > > '22:00:00')
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2,
> > > '10:00:00')
> > > INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('2',
> > > '10:00:00')
> > > CREATE TABLE tmpPlaceReference(PlaceID char(12), Name char(50))
> > > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('1',[/color][/color][/color]
'Here')[color=blue][color=green][color=darkred]
> > > INSERT INTO tmpPlaceReference(PlaceID , Name ) VALUES ('2',[/color][/color][/color]
'There')[color=blue][color=green][color=darkred]
> > >
> > > If the time now is 21:59. I need a query that returns:
> > > Here 22:00:00
> > > There 22:00:00
> > >
> > > If the time now is 22:01
> > >
> > > Here Null
> > > There Null[/color]
> >
> > Your definition of "nearest record" still eludes me. From the narrative,
> > it is not obvious why 22:00:00 should not be returned when current time
> > is 22:01. But the sample output makes it clear what you want.
> >
> > Here is a query that almost gives the desired output. Almost, because
> > it is impossible to return 22:00:00 for There, as this time is not given
> > for There.
> >
> > declare @now datetime
> > select @now = '20040306 21:59'
> >
> > SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
> > FROM dbo.tmpPlaceReference pr
> > LEFT JOIN dbo.tmpDepartures d
> > ON d.endpoint = pr.PlaceID
> > AND d.starttime > convert(char(8), @now, 108)
> > GROUP BY pr.Name
> > ORDER BY pr.Name
> >
> > The convert stuff is need because there is no time data type in SQL
> > Server. SQL Server accepts '10:00:00' for input to a datetime value,
> > but that actually means '19000101 10:00:00'. Convert takes a couple
> > of format codes for datetime values, 108 is for time only.
> >
> > Note that if @now is 23:59 and there is a departure at midnight, that
> > depature will not be listed.
> >
> > Finally a note about your script: it's a good idea to run it and check
> > before you post. I can tell that you hadn't, because the datetime(8)
> > gave me a syntax error.
> >
> > --
> > Erland Sommarskog, SQL Server MVP, sommar@algonet.se
> >
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techinf...2000/books.asp[/color]
>
> Despite my errors with the datetime in CREATETABLE and the fact I got the
> INSERT wrong also..should have been:
>
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES ('1',
> '22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES
> ('2','22:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES
> ('1,'10:00:00')
> INSERT INTO tmpDepartures (endpoint , starttime) VALUES
> ('2','10:00:00')
>
> ... you did well to give me the answer (sorry about that).
>
> All seems to work well .. Many thanks for your help.
>
> Regards
> Richard
>
>[/color]
Now I want to add a new variable

table tmpDepartures has a new column called 'StartPoint'. I need to refinne
the resulting rows to a specific 'StartPoint'

re: DISTINCT QUERY

> table tmpDepartures has a new column called 'StartPoint'. I need to
refinne[color=blue]
> the resulting rows to a specific 'StartPoint'[/color]

You mean just an extra predicate in the WHERE clause?

My solution:

SELECT P.name,
(SELECT MIN(starttime)
FROM tmpDepartures
WHERE endpoint = P.placeid
AND startpoint = /* something */
AND starttime >= CONVERT(VARCHAR,CURRENT_TIMESTAMP,14))
FROM tmpPlaceReference AS P

Erland's solution:

SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
FROM dbo.tmpPlaceReference pr
LEFT JOIN dbo.tmpDepartures d
ON d.endpoint = pr.PlaceID
AND d.startpoint = /* something */
AND d.starttime > convert(char(8), @now, 108)
GROUP BY pr.Name
ORDER BY pr.Name

If that doesn't answer your question, please post revised DDL, sample data
and show your required result.

--
David Portas
SQL Server MVP
--

re: DISTINCT QUERY

Thats great guys. Thanks very much.


"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:9uCdnWYkyPYc89fdRVn-gQ@giganews.com...[color=blue][color=green]
> > table tmpDepartures has a new column called 'StartPoint'. I need to[/color]
> refinne[color=green]
> > the resulting rows to a specific 'StartPoint'[/color]
>
> You mean just an extra predicate in the WHERE clause?
>
> My solution:
>
> SELECT P.name,
> (SELECT MIN(starttime)
> FROM tmpDepartures
> WHERE endpoint = P.placeid
> AND startpoint = /* something */
> AND starttime >= CONVERT(VARCHAR,CURRENT_TIMESTAMP,14))
> FROM tmpPlaceReference AS P
>
> Erland's solution:
>
> SELECT pr.Name, MIN(convert(char(8), d.starttime, 108))
> FROM dbo.tmpPlaceReference pr
> LEFT JOIN dbo.tmpDepartures d
> ON d.endpoint = pr.PlaceID
> AND d.startpoint = /* something */
> AND d.starttime > convert(char(8), @now, 108)
> GROUP BY pr.Name
> ORDER BY pr.Name
>
> If that doesn't answer your question, please post revised DDL, sample data
> and show your required result.
>
> --
> David Portas
> SQL Server MVP
> --
>
>[/color]